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 three 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:

  • Split Column by Delimiter—automatic detection of delimiter character
  • Combine Files—ability to select sample file to use
  • DB2 Connector—option to specify Package Collection

Split Column by Delimiter—automatic detection of delimiter character

When splitting column by delimiter inside Query Editor via Home > Split Column, Excel will automatically detect and populate the Delimiter field in this dialog, based on text pattern detection in the data preview rows. Note that you can still change the delimiter if needed, but this automatic detection saves a few clicks in many cases.



Combine Files—ability to select sample file to use

This month, we continue making incremental enhancements to the Combine Files experience. One of the common areas of recent feedback has been to add the ability to select a file from a folder to use as the sample file to select objects and specify any custom transformation steps (if needed).

With this release, we added a new control in the file preview dialog to allow you to choose a file available in the selected folder. The default selection is the first file found—however, this can now be easily customized by users.



DB2 connector—option to specify Package Collection

The DB2 connector window now includes an option to specify the package collection to connect to within a given DB2 server. This new option, available under the Advanced Options section in the DB2 connector dialog, can only be used with the Microsoft IBM DB2 driver.



Learn more


—The Excel team


Source: May 2017 updates for Get Transform in Excel 2016 and the Power Query add-in - Office Blogs