Back
Excel

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

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

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

Top
10 comments
  1. “Copy and paste queries between Power BI Desktop and Excel” – cool feature!

  2. My Query Options shows Ver 2.24.4065.11301, which is older than my previous Excel 2013 Power Query version. My Office 2016 Updates notifies that I am up to date with Ver 16.0.6001.1068 but I do not have the features shown above. Is there something I am missing?

  3. I do not seem to be able to update to this version of Get & Transform. My Excel 2016 Version is 16.0.6001.1068 and it notifies me as being the latest version of Office.

  4. I didn’t find any changes to “function invocation”. The rest seems alright.

    • Can you please share your Excel version and the experience that you see while trying to invoke a function from the Navigator window?

      Guy Hunkin
      Excel Team

  5. My Office 365 Pro Plus was also stuck on 16.0.6001.1068. I ended using the Manage Account link on the File > Account page, to get to https://portal.office.com/OLS/MySoftware.aspx.

    From there I re-installed Office to (eventually) end up on version 16.0.6568.2036. It has the new features.

    I suspect the regular update process is too simplistic – there isnt any feedback when it doesnt work.

Comments are closed.