New Excel, Power Query features announced

The Office team has just announced several new features for the desktop Excel app that will be available to users through the Get & Transform section of the Data ribbon. These new features are leveraging the Power Query technology which “provides fast, easy data gathering and shaping capabilities” according to the blog post. Among the goodies, users will get access to eight new data transformation features as well as two new data connectivity features, please read all the details below.

Remove blanks via Column Filter menu

Via the Column Filter menu drop-down, users will now be able to access a new data filtering option that will allow them to remove all rows where the value for the current column is null or empty.

Convert Duration values to Years

This new transformation option will allow users to calculate total years based on a Duration type column (Excel will divide the total number of days by 365). To access it, users should look for the ‘Total years” entry under the Transform or Add columns tabs

Keep Duplicates

On the Ribbon, the Home > Remove Duplicates split button will now feature a new “Keep duplicates” command under the previously available “Remove duplicates”. This new transformation option will allow users to keep only the rows with duplicated values on the select column(s)

Hints for “sample input values” in the “Change Type with Locale” dialog

This new transformation option will let users change the type of a column taking into account the Locale or regional settings/formats in which the data is being represented. To use it, users will have to sett the Locale value for the entire workbook (under Data >New Query > Query Options > Current Workbook > Data Load). By doing a right-click on a column header in the Query Editor preview and then selecting Change Type > Using Locale, the “Change Type with Locale” dialog box will allow users to 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

The Query Editor preview will now let users visualizing any whitespace characters (including line feeds) in data cells. While the default behaviour is now set as “show whitespace”, users will still be able to un-toggle it from the  View tab on the Query Editor ribbon.

Ability to disable previews from the Navigator window

Under the “Display options” menu, a new option will allow users to disable preview in the Navigator dialog.

Technical name support in the Navigator window

This new transformation option will allow users to switch between “physical name” and “technical name” on the Navigator dialog, with “Technical names” being “a more meaningful name for the end user connecting to the data source than the “physical name” for the object.”

Rename queries directly from the Queries pane in the Query Editor

Inside the Query Editor, users can now quickly rename queries by either doing a right-click on them, double-clicking them or selecting them and then pressing F2.

Support for Command Timeout in the UX

This new Command Timeout option (available under the “Advanced options” section in data source dialogs) will let users specify a Command Timeout value (in minutes) when connecting to database sources.

Set to disable Privacy Level prompts at machine level (including Registry Key)

As Power Query allows users to combine data from multiple data sources into a single workbook, a feature called “Privacy Levels.” will allow 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.

However, as these different privacy levels can prevent users from using data combinations that bypass this Privacy feature, the Office team will allow users and enterprises to choose one of the following behaviours under the Query Options dialog:

• 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.

All these updates will first roll out to Office 365 subscribers that have enrolled into the Office Insider program. For those of you who are still using Excel 2010 or Excel 2013, it’s still possible to get all these new features by downloading the latest Power Query for Excel add-in over there. Please let us know in the comments what do you think of all these new Excel features.