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

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

The post November 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in appeared first on Office Blogs.