Jump to content


Photo

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


  • Please log in to reply
No replies to this topic

#1 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 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