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

 Get & Transform—a powerful set of Excel 2016 features based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities. These updates—requested by many of our customers—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 transformation features and improved data connectivity features:

• New transformation features:
  • Add Column by Example.
  • Split Column (by Delimiter/Number of Characters) into rows.
  • Basic mode for Group By operation.
  • Extract Text Before/After/Between Delimiters.
  • Unpivot Only Selected Columns.
• Combine Files experience—ability to reference first file as example.
• New Go to Column in Query Editor.
• SAP HANA Connector—enhancements to Parameter Input UX.

Add Column by Example

With this update, we added a new capability to the Query Editor that allows you to create a new column by specifying a set of examples of the desired output. Based on these examples, Excel automatically detects and applies the required transformations.

This is an innovative approach to defining query steps within the Query Editor, which simplifies the process for basic users, as they no longer need to come up with an algorithm or complex logic for how to transform or extract data from other columns or how to define those transformations in the Query Editor.

Split Column (by Delimiter/Number of Characters) into rows

We added a new option in the Split Column by Delimiter and Split Column by Number of Characterstransformation dialogs that allows you to specify whether to split into new columns (previous default behavior) or split into new rows.

This new option can be found under the Advanced Options section in these two dialogs.

Basic mode for Group By operation

The new Basic mode in the Group By transform dialog simplifies the experience to group by a single column and define a single output column. You can switch between Basic and Advanced modes within the Group By dialog by selecting the desired option at the top of the dialog.

Extract Text Before/After/Between Delimiters

We added a new option to extract all text before, after or between delimiters from a Text column. You can find these new commands under the Extract drop-down menu on the Transform and Add Column tabs on the Query Editor ribbon.

Unpivot Only Selected Columns

A new transform was added under the Unpivot Columns menu in Query Editor that allows you to unpivot only the currently selected columns in the Query Editor preview. This generates an explicit columns list in the current step so that the same set of columns is unpivoted on future refresh operations.

In addition to the newly added Unpivot Only Selected Columns option, you can also apply one of the following (and previously familiar) commands from the Unpivot Columns menu:

• Unpivot Columns—This operation will unpivot all columns except all the non-selected ones. This case is optimized for scenarios where new columns that appear in the future need to be unpivoted as well. For example, datasets where new columns represent data for new dates (i.e., monthly sales, weekly occurrences, etc.).
• Unpivot Other Columns—This operation provides the same capability as Unpivot Columns in terms of future behavior with respect to new columns appearing in the table. The main difference with Unpivot Columns is that it allows you to select the columns that should not be unpivoted. This case optimizes the user flow for cases where the number of columns that should not be unpivoted is much smaller than the number of columns that should be unpivoted, similar to the behavior of Remove Other Columns compared to Remove Columns.

Combine Files experience—ability to reference first file as example

We improved the Combine Files experience by allowing you to always reference the first file in a folder as the example file. Before this update, you had to pick a specific file by name, which might cause errors in the future if such file is removed from the folder.

With this update, you can select First file in the Combine Files dialog, which will ensure that the first file in the folder is used as the example, regardless of the specific file name.

New Go to Column in Query Editor

When working with tables that have many columns, it is hard for users to scroll in the Query Editor preview to find the column that they want to apply transformations to.

In this month’s release, we added a new Go to Column command to the Home tab on the Query Editor ribbon that allows you to search within the list of columns and select the column that they would like to scroll into preview and select.

SAP HANA connector—enhancements to Parameter Input UX

We improved the Parameter Input UX for the SAP HANA connector in a couple of ways:

• You can now filter down the list of parameters to only required parameters.
• We moved away from drop-down menus for single/multi-selection input controls and started using a popup dialog for both scenarios. This new dialog allows users to see both the ID and Caption for each parameter value, as well as search within the list of values by any of these two fields. This makes it easier to provide input values, as opposed to the previous experience that only allowed input IDs.