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 10 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 transformation features:
- Remove blanks via Column Filter menu.
- Convert Duration values to Years.
- Keep Duplicates.
- Hints for “sample input values” in the “Change Type with Locale” dialog.
- Support for whitespace and line feeds in Query Editor preview.
- Ability to disable previews from the Navigator window.
- Technical name support in the Navigator window.
- Rename queries directly from Queries pane in the Query Editor.
And the following new or improved data connectivity features:
- Support for Command Timeout in the UX.
- Set to disable Privacy Level prompts at machine level (including Registry Key).
Remove blanks via Column Filter menu
With this update, we have added a new data filtering option that will remove all rows where the value for the current column is null or empty. It can be accessed via the Column Filter menu drop-down.
Convert Duration values to Years
This new transformation option can be found under the Transform or Add Column tabs. Within the Duration drop-down menu there is a new “Total Years” entry that allows you to calculate total years based on a Duration type column. The logic applied is to divide the total number of days by 365.
This new transformation allows users to keep only the rows with duplicated values on the select column(s). Before this update, only “Remove Duplicates” was available. This new option can be found on the ribbon under Home > Remove Duplicates split button and then select the Keep Duplicates command.
Hints for “sample input values” in the “Change Type with Locale” dialog
Power Query allows users to change the type of a column taking into account the Locale or regional settings/formats in which the data is being represented. This can be done by setting the Locale value for the entire workbook (under Data > New Query > Query Options > Current Workbook > Data Load) and also can be customized for each specific “Change Type” step in the queries.
Within the “Change Type with Locale” dialog (accessible by right-clicking on a column header in the Query Editor preview then selecting Change Type > Using Locale…), users can now get a few “sample values” for the expected input format when selecting a specific data type and locale.
Support for whitespace and line feeds in the Query Editor preview
With this update, we have added support for visualizing whitespaces in data cells within the Query Editor preview. This includes any whitespace characters, including line feeds.
You can toggle between “Show whitespace” (default behavior) and not showing it (old behavior) from the View tab on the Query Editor ribbon.
Ability to disable previews from the Navigator window
With this update, we have added an option to disable previews in the Navigator dialog. This allows users to reduce the number of calls being made to the data source in order to retrieve these previews.
Technical name support in the Navigator window
Some data sources support the notion of a “technical name” for objects within the source. These “technical names” are meant to provide a more meaningful name for the end user connecting to the data source than the “physical name” for the object. In this update, we added a new option to the Navigator dialog to allow users to switch between “physical name” (previous behavior) and “technical name” (new behavior).
Rename queries directly from the Queries pane in the Query Editor
With this update, it is now possible to rename queries directly from the Queries pane inside the Query Editor. To rename a query from this pane, simply select and right-click the query and select Rename, or double-click the query name, or select it and then press F2.
Support for Command Timeout in the UX
With this update, users are now able to specify a Command Timeout value (in minutes) when connecting to database sources. This is a significant experience improvement since, before this update, this customization was only possible via custom formula authoring.
This new Command Timeout option can be found under the “Advanced options” section in data source dialogs.
Set to disable Privacy Level prompts at machine level (including Registry Key)
Power Query allows users to combine data from multiple data sources into a single workbook. When dealing with multiple data sources, it is possible for users to define queries that require sending data from one data source to another data source. To prevent accidental disclosure of private or enterprise data, Power Query provides a feature called “Privacy Levels.” This feature allows users to specify the Privacy Level (Public, Organizational or Private) for each data source that they connect to when trying to combine data from multiple sources. Based on the selected Privacy Level, Power Query will ensure that data from a Private source is not sent to any other sources and that data from an Organizational source is only sent to sources within the organization.
In some cases, this privacy enforcement can get in the way for users who want to define some data combinations that bypass this Privacy feature. Another potential effect of Privacy Levels is that, when combining data from multiple sources that are not supposed to send data between them due to their Privacy Levels, Power Query will compensate and download the data locally to perform the data combination in a secure way. This could result in a performance degradation due to the fact that data would have to be cached locally from all sources implied and combined in-memory. For those cases, users have the ability to ignore Privacy Levels as a “current workbook” setting.
However, given that this option was only available per file and per user, it would require a user to enable this option for each workbook they would like to leverage. In addition, ignoring this privacy protection needs to be approved by each user of the workbook, so someone opening a .XLSX file from a different user in their computer would have to either provide Privacy Levels for the data sources involved on the report or manually disable this feature in the Options dialog (under Query Options > Current Workbook > Privacy section).
In this release, we’re introducing a new setting to allow users and enterprises to pick one of the following behaviors:
- Always combine data according to your Privacy Level settings for each source—This new option allows a user to “enforce” that Privacy Levels are taken into account for every .XLSX file on their machine, regardless of whether the “Current Workbook” Privacy Level setting is enabled or disabled.
- Combine data according to each file’s Privacy Level settings—This is the default behavior and matches the Power Query behavior in previous releases.
- Always ignore Privacy Level settings—This new option allows a user to always bypass Privacy Level settings for every .XLSX file in their machine, regardless of the Current Workbook setting.
These options are available to the user under the Query Options dialog:
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.
- Learn more about what’s new in Excel 2016.
- Join our Excel community on Facebook and Twitter.
- Send us your ideas for other improvements on our Excel UserVoice page.
—The Excel team