Jump to content


Version 1603 - April 4, 2016 - Get & Transform improvements

  • Please log in to reply
No replies to this topic

#1 Jonathan


    Forums Administrator

  • Administrators
  • 880 posts

Posted 10 May 2016 - 11:57 AM

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.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users