Share this article

With KB5043178 to Release Preview Channel, Microsoft advises Windows 11 users to plug in when the battery is low

Copilot in Outlook will generate personalized themes for you to customize the app

Microsoft will raise the price of its 365 Suite to include AI capabilities

Death Stranding Director’s Cut is now Xbox X|S at a huge discount

Outlook will let users create custom account icons so they can tell their accounts apart easier

New Excel, Power Query features announced

5 min. read

Published onMay 23, 2016

published onMay 23, 2016

Share this article

Read our disclosure page to find out how can you help Windows Report sustain the editorial teamRead more

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

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.

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

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)

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.

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.

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

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

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.

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.

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:

All these updates will first roll out to Office 365 subscribers that have enrolled into theOffice 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-inover there. Please let us know in the comments what do you think of all these new Excel features.

Radu Tyrsina

Radu Tyrsina has been a Windows fan ever since he got his first PC, a Pentium III (a monster at that time).

For most of the kids of his age, the Internet was an amazing way to play and communicate with others, but he was deeply impressed by the flow of information and how easily you can find anything on the web.

Prior to founding Windows Report, this particular curiosity about digital content enabled him to grow a number of sites that helped hundreds of millions reach faster the answer they’re looking for.

User forum

0 messages

Sort by:LatestOldestMost Votes

Comment*

Name*

Email*

Commenting as.Not you?

Save information for future comments

Comment

Radu Tyrsina

With KB5043178 to Release Preview Channel, Microsoft advises Windows 11 users to plug in when the battery is low#

Copilot in Outlook will generate personalized themes for you to customize the app#

Microsoft will raise the price of its 365 Suite to include AI capabilities#

Death Stranding Director’s Cut is now Xbox X|S at a huge discount#

Outlook will let users create custom account icons so they can tell their accounts apart easier#

New Excel, Power Query features announced#