Back
Excel

November 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 based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.

Today, we are pleased to announce 10 new data transformation and connectivity features that have been requested by customers.

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 take advantage of these updates by downloading the latest Power Query for Excel add-in.

Updates include the following new or improved data connectivity and transformation features:

  • Web Connector—UX support for specifying HTTP Request Headers.
  • OData Connector—support for “Select Related Tables” option.
  • Oracle Connector—improved Navigator preview performance.
  • SAP HANA Connector—enhancements to parameter input UX.
  • Query Dependencies view from Query Editor.
  • Query Editor ribbon support for scalar values.
  • Add custom column based on function invocation.
  • Expand & Aggregate columns provide support for “Load More” values.
  • Convert table column to a list—new transformation.
  • Select as you type in drop-down menus.

Web Connector—UX support for specifying HTTP Request Headers

With this update, we have added support for specifying HTTP Request Headers within the Web connector dialog. Users can specify header name/value pairs from the Advanced mode in this dialog.

november-2016-updates-for-get-transform-in-excel-1

OData Connector—support for “Select Related Tables” option

In this update, we have added relationship detection support for OData V4 feeds. When connecting to OData V4 feeds, users will be able to use the Select Related Tables button in the Navigator dialog to help you easily select all the tables needed for your reports.

november-2016-updates-for-get-transform-in-excel-2

Oracle Connector—improved Navigator preview performance

We significantly improved the performance for previewing tables in the Navigator dialog when connecting to Oracle databases. In most cases, previews should take up to 30 percent less time to display than before.

SAP HANA Connector—enhancements to parameter input UX

We made enhancements to the parameter input controls within the Navigator dialog when connecting to SAP HANA. These enhancements include:

  • For single-selection parameters:
    • Support for typing values, in addition to selecting values from the drop-down list.
    • Member ID and Member Caption are now exposed within the drop-down list.
  • For multi-selection parameters:
    • Member ID and Member Caption are now exposed within the drop-down list.
    • Users can leverage the Search box to search across both Member ID and Member Caption fields.

Query Dependencies view from Query Editor

One of the most popular requests in the Data preparation area for the past several months has been having the ability to easily understand dependencies across all queries in a given workbook. In this month’s release, we added a Query Dependencies view in the Query Editor (under the View tab on the ribbon) that allows users to see all queries and dependencies on other queries or data sources at a glance—as well as an indicator of their Load status.

november-2016-updates-for-get-transform-in-excel-3

Query Editor ribbon support for scalar values

We added Query Editor ribbon support for common operations when dealing with scalar-type queries. This includes new Contextual Ribbon tabs that expose common transformations for Text, Date/Time and Number values.

november-2016-updates-for-get-transform-in-excel-4

Add custom column based on function invocation

A common scenario when working with custom functions is invoking such function over each row in a table. Before this update, users had to create custom columns and manually type an M expression to invoke a custom function with the right set of arguments.

With this update, we made it seamless for users to invoke a custom function over a table. Users can now simply leverage the new Invoke Custom Function operation from the Add Column tab on the ribbon.

november-2016-updates-for-get-transform-in-excel-5

This dialog gives you the option to pick one of the custom functions defined in the current workbook and specify input arguments, which may include static values or references to other columns in the current table query.

november-2016-updates-for-get-transform-in-excel-6

Expand & Aggregate columns provide support for “Load More” values

When dealing with columns with nested data structures, such as Records or Tables, it is a common that all nested structures do not have the same schema. Excel leverages schema detection to provide information such as the list of nested columns in the Expand & Aggregate column experiences. Before this update, Excel would only look at the top few rows of nested data to construct the list of columns to display.

With this update, we have added the Load More option to the Expand & Aggregate columns list in Query Editor so that users can tell Excel to look at more rows to detect additional nested fields/columns. When using the Load More option, Excel scans up to the top 1,000 rows of nested data to calculate the list of columns to display in the Expand & Aggregate columns list.

Convert table column to a list—new transformation

With this update, we made it much easier for users to turn a given column within a table into a list. Simply select the column in the Query Editor Preview and use the newly added Convert to List button in the Transform tab on the ribbon.

november-2016-updates-for-get-transform-in-excel-7

The output lists can be further transformed by using the contextual Lists Tools ribbon tab to perform common operations (such as Remove Duplicates, Sort, Perform Statistic Calculations, etc.). List Queries can also be used as input for the Accepted Values property in Query Parameters.

november-2016-updates-for-get-transform-in-excel-8

Select as you type in drop-down menus

With this update, we considerably improved the navigation and selection interactions within drop-down menus with the addition of select-as-you-type capabilities. This allows users to type one or more characters and these will get matched with the prefix of drop-down menu items. This behavior is now available in all Get & Transform drop-down menus, but it is particularly useful for menus with many list items (such as the Change Type with Locale dialog or the single-value parameter lists for SAP HANA, etc.).

november-2016-updates-for-get-transform-in-excel-9

How do I get started?

Excel 2016 provides a powerful set of capabilities for fast, easy data gathering and shaping, which is available under the Get & Transform section on the Data ribbon. 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
13 comments
  1. Still need a way to get cursored queries that require multiple hits on a REST endpoint to get all the data.

  2. Thanks! Nice update, continuous improvement, UI is getting better and better! Good that you showed “Change Type with Locale”, really it is a hidden gem in current version.

    • Thanks for your feedback, Ivan.

      Guy
      – Excel Team

  3. As well as being able to set the destination of a Power Query query (worksheet vs. data model) it would be useful to be able to disable loading completely, e.g. if the query only exists to be joined into another query. This is already possible in Power BI.

    • You can already leave a query as a connection only, can you not? Even with Excel, don’t have to load it.

    • Hi Chris,

      We already support this functionality in Excel.

      In the Navigator window (this is where you can select the tables to be loaded and see the preview of the data) click the little arrow to the right of the “Load” button and choose “Load To”. In the next dialog to open, select “Only Create Connection” option, make sure that the “Add this data to the Data Model” checkbox is unchecked and click Load.

      Alternatively, if you are within the Query Editor window, click the bottom of the “Close & Load” button on the ribbon and choose “Close & Load To”. Select “Only Create Connection”, un-check the “Add this data to the Data Model” option and click Load.

      You can then use your connection-only query in further merge/append operation as you need.

      Guy
      – Excel Team

  4. Nice update. Today i tried it with Excel 2013 and the new PowerQuery AddIn.
    But one question: When will this update be available for the regular Office 2016 version?
    Currently, I’m running
    Office Professional Plus 2016 Version 1610 (Build 7466.2038)
    which tells me that it’s up to date.
    But the Get&Transform does not contain the November update, right?
    Will Office 2016 ever get it?

    Regards
    Olaf

    • Hi Olaf,

      If you have an Office 365 subscription on your machine, then you will get these updates, as well. With Office 2013 and the Power Query add-in you are getting the core Power Query features, but you won’t be getting the new connectors that we constantly add to the product.

      As to your question, then I am not aware of any plans to ship these updates for Office perpetual (you called the “regular”) customers.

      Guy
      – Excel Team

  5. Great updates, thank you. One question though… where did the “Browse” button go when trying to update data sources? It seems that you can now only type in the filename which is a nightmare for the way we use this in our organization.

    I’m running Excel 2013-32bit. The option I’m looking for is in Power Query > Data Source Settings > Change Source > [Browse for files?!].

    Thanks!

    • This is a known issue that will be fixed in the next update – Thanks for the feedback.

    • Hi Sameer,

      Do you mind to elaborate on your scenario? What is it that you are trying to achieve?

      Guy
      – Excel Team

Comments are closed.