Microsoft Excel 2016 comes with a set of features called Get & Transform, which helps with data gathering and shaping capabilities.
Get & Transform helps users to connect, combine, and refine data sources to meet their analysis needs.
Back in January, Microsoft added several new features to Get & Transform. These features included a new connector, support for percentage data type, and many improvements.
Now, the company has announced the following five new data transformation and connectivity features for Get & Transform:
- “Support for the same file extensions in Text and CSV connectors.
- ODBC and OLE DB connectors—support for Select Related Tables.
- Enhanced Folder connector—support for “Combine” from the Data Preview dialog.
- New Change Type Using Locale option in Column Type drop-down menu inside Query Editor.
- New Insert Step After option in Steps pane inside Query Editor.”
These updates are available for free as part of an Office 365 subscription and users can start using them now. In addition, if a user has Excel 2010 or Excel 2013, they can also take advantage of these updates by downloading the Power Query for Excel add-in.
How to use the new features
As mentioned above, back in January, Microsoft added six new features for Get & Transform in Excel 2016. Now, with the April 2017 update, users of the set of features get their hands on even more. Here is what’s new.
Thanks to this update, users can now select to import from any text (*.txt), comma-separated value (*.csv) or formatted text space delimited (*.prn) file. Alternatively, they can switch to the All Files (*.*) option and select to import from any other unlisted file.
Concerning the use of ODBC and OLE DB connectors, this update allows users to enable the Select Related Tables button in the Navigator dialog. This option allows users to easily select tables that are directly related to the set of already selected tables in the Navigator dialog.
The April 2017 update for Get & Transform also brings an easier way for users to access the Combine Binaries feature. The Combine Binaries allows users to combine multiple files directly from the folder Data Preview dialog within the Get Data flow. Thanks to the update, users can now do so without having to go into the Query Editor.
Thanks to the latest additions, users can now find the Change Type Using Locale option to the Column Type drop-down menu. This option allows you to specify the desired column type and locale to use for the conversion.
Finally, the April 2017 update adds a new context menu option in the Query Editor window, called Insert Step After. This option lets users insert a new custom step right after the currently selected step.
