Skip to main content
Skip to main content
Microsoft 365

January 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

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.

Get and Transform January updates 1

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.

Get and Transform January updates 2

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.

Get and Transform January updates 3

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.

Get and Transform January updates 4

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

—The Excel team

You may also like these articles

Image for: View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365

View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365

This month, we're announcing some top requested features like tracking changes in Excel and live transcriptions in Teams, as well as some soon-to-be-favorites like meeting recaps, turning Word documents in presentations, and more.

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…