Back
Excel

6 updates for Power Query for Excel

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

Today’s post was written by Miguel Llopis, program manager on the Power Query team.

A Power Query for Excel update is now available for you and is packed with lots of new features including:

  • New Navigator dialog
  • Ability to create new queries from the Query Editor
  • New Launch Editor button on Power Query ribbon tab
  • Additional operators for date filtering in Query Editor
  • Database connections—added support for Port number
  • Additional Performance improvements

button (3)

 

You can continue reading below for more details about each feature.

New Navigator dialog

The new Navigator dialog replaces the Navigator task pane, giving you search capabilities, an enhanced preview experience and optimized layout.

  • Ability to search items in the tree—This has been a very common request for several months. The new Search box in the Navigator dialog enables users to more easily find items in the tree.
  • Enhanced preview experience—Because the data previews are now part of the dialog (instead of in a small size fly-out), it is much easier to preview tables and use the scroll bar control. Similarly, the new preview experience is much more interactive for multi-dimensional sources (such as Analysis Services or SAP BusinessObjects), allowing users to immediately preview the output as they select items in the tree.
  • Optimized layout when in “multi-selection mode”—The layout in this dialog has been optimized to allow for a greater number of items to show up in the tree. The new Show Selected control brings up the list of selected items in a secondary view. This allows the main view to be optimized around browsing, previewing and selecting items.

6 updates for Power Query for Excel 1

Ability to create new queries from the Query Editor

Another commonly requested feature was the ability to connect to external data sources and create new queries without having to leave the Query Editor dialog. We added a New Query section on the Home ribbon tab in the Query Editor, which provides access to all data connectors and recent sources. This allows users to focus on importing and transforming data across all of their queries without having to leave the Query Editor dialog.

6 updates for Power Query for Excel 2

New Launch Editor button on Power Query ribbon tab

With the New Query section added to the Query Editor and the ability to work with multiple queries in this dialog, it becomes much more desirable to be able to launch the Query Editor dialog from the Power Query tab in Excel. The new Launch Editor button lets users get directly into the Query Editor from Excel.

6 updates for Power Query for Excel 3

Additional operators for date filtering in Query Editor

We have added a few additional filter options for Date/Time columns in the Query view including Year to Date, Today, This Week, This Month and This Quarter. These options are displayed under the respective drop-down menu in the Date filters.

6 updates for Power Query for Excel 4

Note that all of these filters use dynamic calculations, so for instance, Year to Date will always calculate from January 1 to the present day.

Database connections—added support for Port number

It is now possible to specify a Port number when connecting to a database. This can be done by using Server:Port in the Server Name field within one of the database source dialogs.

Performance improvements

We continue making incremental improvements to Performance in the product. This month there are two main areas where Performance has been improved:

  • A 40-50 percent improvement when loading database tables without filters/transforms.
  • Overall performance improvements to the Exchange Connector.

That’s all for this update. We hope that you enjoy these new features and continue sending us feedback about the product. We have many other new features coming in the next few months so please stay tuned for future updates.

button (3)

 

 

—Miguel Llopis

———————-

Power Query for Excel is available with an Office 365 ProPlus subscription, Office 2010 Professional Plus with Software Assurance, Office 2013 Professional Plus or Excel 2013 Standalone.  Download the add-in and learn more about getting started.

Learn about all the powerful analytics and visualization features in Excel and take your analysis further by sharing and collaborating on business insights with colleagues using Power BI.

Top