July 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 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 12 new data transformation and connectivity features that have been requested by many 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 also take advantage of these updates by downloading the latest Power Query for Excel add-in.

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

  • New SAP HANA connector.
  • New SharePoint Folder connector.
  • New Online Services connectors category.
  • Improved DB2 connector, now leveraging the Microsoft driver for IBM DB2.
  • Improved Text/CSV connector, now exposing editable settings in the preview dialog.
  • Improved relational database connectors, now including Schema information as a part of the Navigation hierarchy.
  • Data Source Settings enhancements, including “Change Source” capability.
  • Advanced Filter Rows dialog mode within the Query Editor.
  • Inline Input controls for Function invocation within the Query Editor.
  • Support for reordering Query Steps within the Query Editor by using drag and drop gestures.
  • Date picker support for input Date values in Filter Rows and Conditional Columns dialogs.
  • New context menu entry to create new queries from the Queries pane within the Query Editor.

New SAP HANA connector

In this update, we added a new connector to allow users to import data from SAP HANA databases. The new connector can be found under Data > New Query > Database category, or from the Query Editor window.

July 2016 updates for Get Transform in Excel 2016 1

New SharePoint Folder connector

Available under Data > New Query > File category, the new SharePoint Folder connector enables users to import data from multiple files within a SharePoint folder, similar to how the existing Folder connector enables users to import and combine multiple files from a filesystem folder.

July 2016 updates for Get Transform in Excel 2016 2

New Online Services connectors category

With this update, we added a new category that includes all available connectors for Online Services in Excel. The new category is available under Data > New Query > Online Services or from the Query Editor window.

July 2016 updates for Get Transform in Excel 2016 3

Improved DB2 connector, now leveraging the Microsoft driver for IBM DB2

In this release, we improved the IBM DB2 connector to provide users the choice to leverage the Microsoft driver for IBM DB2, which is also automatically included in the Excel/Power Query installation.

Within the IBM DB2 connector dialog, users can now select which driver to use under Advanced options.

July 2016 updates for Get Transform in Excel 2016 4

Improved Text/CSV connector, now exposing editable settings in the preview dialog

In this update, we improved the Text and CSV connectors so users can configure basic import settings from within the preview dialog. These import settings include:

  • File Origin.
  • Delimiter.
  • Detect Data Type (strategies include: Base on top 200 rows, Base on entire dataset or No data type detection).

Based on user choices, the preview in this dialog automatically updates. Users can then decide whether to directly load the data or edit first to apply additional data transformations.

July 2016 updates for Get Transform in Excel 2016 5

Improved relational database connectors, now including Schema information as a part of the Navigation hierarchy

In this update, we enhanced the database connectors to expose an option that allows users to include Schema information as part of the Navigation hierarchy. This option is available under the Advanced option in all database connector dialogs, such as the SQL Server Database dialog below:

July 2016 updates for Get Transform in Excel 2016 6

Data Source Settings enhancements, including “Change Source” capability

Users can now easily update the location of a data source for all queries connected to that data source within a single Excel workbook. Before this update, users had to update the data source location for each of the queries connected to the source. The new solution allows users to do this in a single step.

Within the Data Source Settings dialog (Data > New Query > Data Source Settings), there is a new Current Workbook scope option, which allows users to limit the list of displayed data sources to only those that are used in the current file. From this view, the users can modify credentials, privacy levels and other settings for that data source, or use the Change Source option to modify the location for that data source.

July 2016 updates for Get Transform in Excel 2016 7

Advanced Filter Rows dialog mode within the Query Editor

We’re introducing a new mode in the Filter Rows dialog within the Query Editor. This new mode allows users to add multiple filter clauses within a single Filter Rows step (before this update, only 1–2 clauses were allowed) and also combine filter clauses based on multiple columns (before, all clauses were applied to a single column).

July 2016 updates for Get Transform in Excel 2016 8

Inline Input controls for Function invocation within the Query Editor

We improved the inline preview for functions within the Query Editor dialog to allow input values for function invocation. This new Enter Parameters section in the inline function preview is available for both “out of the box” functions (M Engine functions) as well as user-defined functions.

July 2016 updates for Get Transform in Excel 2016 9

Support for reordering Query Steps within the Query Editor by using drag and drop gestures

We now support reordering of Query Steps within the Query Editor by using drag and drop gestures. This can be done for each query via the Applied Steps section in the Query Editor.

July 2016 updates for Get Transform in Excel 2016 10

Date picker support for input Date values in Filter Rows and Conditional Columns dialogs

In this update, users can now leverage a Date picker control to provide input Date values in the Filter Rows and Conditional Columns dialogs.

July 2016 updates for Get Transform in Excel 2016 11

New context menu entry to create new queries from the Queries pane within the Query Editor

You can now add a new query in the Queries pane within the Query Editor when you right-click in the background of the pane or in a query group node. This allows a more intuitive experience for adding new queries when working in the Query Editor pane. Also, when you right-click a query group, the new query is added to the selected group.

July 2016 updates for Get Transform in Excel 2016 12

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

—The Excel team