March 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

Excel 2016 includes a powerful new set of features called Get & Transform, which provides fast, easy data gathering and shaping capabilities. Today, we are pleased to announce another update to the powerful feature set. 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.

This update includes the following new or improved Data Transformation features:

  • Filter by “not earliest/latest date.”
  • Filter by “is in previous N minutes/hours/seconds.”
  • Copy and paste queries between Power BI Desktop and Excel.
  • Support for Special Characters in Split Column.
  • Refresh previews in Merge Queries dialog.
  • Monospaced font for Query Editor Preview.
  • Improved function invocation experience.
  • Option to set credentials at the server level.
  • Add prefix/suffix to a Text column.

Filter by “not earliest/latest date”

We added a new Date/Time columns filter option in the Query Editor to allow users to filter by dates that are not the earliest or latest dates within the current column.

Updates to Get & Transform in Excel 2016 and the Power Query add-in 1

Filter by “is in previous N minutes/hours/seconds”

Another new Date/Time filter provides the ability to filter out values that are within the previous N minutes/hours/seconds. This can be accessed from the In the Previous… option under the Filter menu for Date/Time columns.

Updates to Get & Transform in Excel 2016 and the Power Query add-in 2

You can then define the filtering options by specifying the value and the desired filtering scope from the drop-down. Note the new hours, minutes and seconds options added in this update.

Updates to Get & Transform in Excel 2016 and the Power Query add-in 3

Copy and paste queries between Power BI Desktop and Excel

We know that users often work with multiple tools in their daily activities and use Excel alongside Power BI Desktop for dashboarding and sharing scenarios. In a previous update, the ability to copy and paste queries between different Excel workbooks was enabled. This update makes it seamless for users to copy and paste their queries between Excel and Power BI Desktop. Users can now right-click a query or a query group in the Queries task pane in Excel workbook to copy those queries. They can then paste them into the Queries task pane in Query Editor for Power BI Desktop. Copying the queries from the Power BI Desktop into Excel is done in a similar way.

Support for Special Characters in Split Column

Previously, we added support for using Special Characters in the Replace Values dialog to find and replace values. In this update, we enabled the same functionality for Split Column by Delimiter operations.

Updates to Get & Transform in Excel 2016 and the Power Query add-in 4b

Refresh previews in Merge Queries dialog

It is now possible to refresh the table previews within the Merge Queries dialog so users can select which columns to match using the latest available data.

Updates to Get & Transform in Excel 2016 and the Power Query add-in 5

Monospaced font for Query Editor Preview

Users can now customize the Query Editor Preview to display content using a monospaced font. This customization option is found under the View tab.

Updates to Get & Transform in Excel 2016 and the Power Query add-in 6

Improved function invocation experience

It is now possible to invoke functions directly from within the Navigator dialog. This allows users to invoke and preview results of function invocation against a data source (such as SQL Server).

Users can now specify the function parameters directly from the Navigator window:

Updates to Get & Transform in Excel 2016 and the Power Query add-in 7

And then preview the results in place:

Updates to Get & Transform in Excel 2016 and the Power Query add-in 8

Option to set credentials at the server level

Users are now given the chance to set the scope for their credentials when prompted for Database credentials. This is available as a radio button option at the bottom of the Credentials dialog.

Updates to Get & Transform in Excel 2016 and the Power Query add-in 9

Add prefix/suffix to a Text column

It is now possible to add a prefix/suffix to an existing Text column via Query Editor—either by using this option under Transform (modify column in place) or under Add Column to create a new column (available under Text Column > Format).

Updates to Get & Transform in Excel 2016 and the Power Query add-in 10

How do I get started?

Get & Transform is a set of capabilities for fast, easy data gathering and shaping, which is available in Excel 2016. Updates outlined in this blog 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.

—The Excel team