Excel 2016 get eight new data transformation and connectivity features for Get & Transform.
- 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.
These updates are available for free as part of an Office 365 subscription.
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.
We added a new option in the Split Column by Delimiter and Split Column by Number of Characters transformation 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.
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.
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.
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.
- 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.
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.
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.