Excel 2016 includes a powerful set of features based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.
Today, we are pleased to announce six new data transformation and connectivity features that have been requested by many customers.
These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.
These updates include the following new or improved data connectivity and transformation features:
- New OLE DB connector.
- Enhanced “Combine Binaries” experience when importing from any folder.
- Maximize/Restore buttons in the Navigator and Query Dependencies dialogs.
- Support for percentage data type.
- Improved “Function Authoring” experience.
- Improved performance for OData connector.
New OLE DB connector
In this update, we enabled connectivity to OLE DB drivers via the new OLE DB connector. In addition to the wide range of out-of-the-box sources supported, OLE DB greatly increases the number of sources that users can now import from by using Get & Transform capabilities in Excel.
The new OLE DB connector can be found under Data > New Query > From Other Sources > From OLE DB.
The connector dialog allows users to specify a Connection String and, optionally, an SQL statement to execute. If no SQL statement was specified, users will be taken into the Navigator dialog, where they can browse and select one or multiple tables available via the selected OLE DB driver.
Enhanced “Combine Binaries” experience when importing from any folder
One of the most popular scenarios in Excel consists of leveraging one of the folder-like connectors (such as Folder, SharePoint folder, etc.) to combine multiple files with the same schema into a single logical table.
Before this release, users could combine Text or CSV files only. The combine would not work for any other supported file formats (such as Excel Workbooks, JSON files, etc.), and it would not account for transformations required on each file before combining them into a single table (such as removing the first row with header values).
With this release, we enhanced the “Combine Binaries” experience when importing from any folder so that:
- Excel analyzes the input files from the Folder query and detects the right file format to use (i.e., Text or Excel Workbook).
- Users can select a specific object from the list (such as a spreadsheet name) to use for data combine.
- Excel automatically creates the following entities:
- An example query that performs all required transformation steps in a single file.
- A function query that parameterizes the file input to the exemplar query created in the previous step.
- Excel then applies the created function query on each file from the original Folder query and expands the resulting data extraction as top-level columns.
With this new approach, users can easily combine all binaries within a folder if they have a homogeneous file type and column structure. Users can also easily apply additional transformations by modifying the “exemplar query” without having to worry about any additional function invocation steps, as they’re automatically generated for them.
Maximize/Restore buttons in the Navigator and Query Dependencies dialogs
The Navigator and Query Dependencies dialog (activated from Query Editor) support window resizing by dragging the bottom-right edges of the dialog. In this release, we made it possible to maximize/restore these dialogs by exposing Maximize and Restore icons in the top-right corner of the dialogs.
Support for percentage data type
With this update, we added support for percentage data types, so they can easily be used in arithmetical operations for Get & Transform scenarios. An input value such as “5%” will be automatically recognized as a percentage value and converted to a two-digit precision decimal number (i.e., 0.05), which can then be used in arithmetical operations within a spreadsheet, the Query Editor or the Data Model.
Besides automatic type recognition from non-structured sources (such as Text, CSV or HTML), users can also convert any value to percentage using the Change Type options in the Query Editor. You can do this on the Query Editor Home tab, on the Transform tab, by clicking Data Type > Percentage, or right-clicking a column and then selecting Change Type > Percentage.
Improved “Function Authoring” experience
We also made it easier to update function definitions without the need to maintain the underlying M code.
Here’s how it works: Create a function based upon another query using the “Create Function” command. You do this by right-clicking the Queries pane inside Query Editor. When you do that, a link will be created between the original query and the newly generated function. This way, when the user modifies the original query steps, the linked function will be automatically updated as well.
When using Query Parameters, creating a function out of a query will allow users to use Function Inputs to replace parameter values in the generalized function query.
Improved performance for OData connector
With this update, we added support for pushing Expand Record operations to be performed in the underlying OData service. This will result in improved performance when expanding records from an OData feed.
- Learn more about what’s new in Excel 2016.
- Join our Excel community on Facebook and Twitter, and collaborate with others on Get & Transform Data at our new Tech Community forum.
- Send us your ideas for other improvements on our Excel UserVoice page.
—The Excel team
The post January 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in appeared first on Office Blogs.