Version 1603 (Build 16.0.6769.2015)
Released to Current Channel on April 4, 2016
Get & Transform is a new feature in Excel 2016 that is fully explained in our Excel 2016 Expert Skills book. Get & Transform brings features that were previously part of PowerPivot and integrates them into Excel.
This update makes several improvements to Excel 2016's Get & Transform features.
New filter options
This update adds several new filter options to the Get & Transform Query Editor. These include:
- Is Not Earliest (date)
- Is Not Latest (date)
- In the Previous (X hours/minutes/seconds)
Copy and paste queries from Power BI
If you also use the standalone Power BI product, you can now copy and paste queries directly into the Queries task pane within Excel's Get & Transform Query Editor.
Splitting delimited columns using special characters
It's a common requirement to split one column into several columns by searching for a delimiter character (such as a comma). This update enables you to split columns using special characters, including tabs, carriage returns and line feeds.
The concept of delimited data is explained in the video lesson: Expert Skills Lesson 2-2 Split delimited data using Text to Columns.
Refresh button in Merge Queries dialog
A new refresh button has been added to the Get & Transform Merge Queries dialog, enabling you to refresh the displayed data without closing the dialog. This could be useful if your data changes rapidly.
Monospaced option in Query Editor
The Get & Transform Query Editor now has a new View > Font > Monospaced option. This will display all of the values shown in the preview pane using a monospaced font, meaning all numbers and letters will be the same width and line up with each other. This makes it easier to compare values on different rows.
Database credentials options
If connecting to an external database, you often need to provide a valid username and password. Previously, it was necessary to do this for every database that you connected to. This update allows you to specify a username and password to use for every database on the same server, making it easier to work with multiple databases that reside on a single server.
Add Prefix and Add Suffix options
Two new commands have been added to the Query Editor: Transform > Add Column > Format > Add Prefix and Transform > Add Column > Format > Add Suffix. These enable you to quickly add text to the beginning or end of any text column.