3 updates to Excel Power Query

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

A new update for Excel Power Query is now available to you! This update includes tremendous Performance improvements, a new Microsoft Dynamics CRM Online connector and a couple of new transformations supported in the UX.

Please take a look at the details below and download this update from the Official Power Query download page.

What’s new in this update?

The following features have been added or improved in this update:

  • Performance improvements
  • Microsoft Dynamics CRM Online connector
  • New transformations

Watch the following video and then continue reading below for more details.

Performance improvements

We’ve made significant Performance improvements to Power Query in a couple of areas:

  • Query load—Performance of loading queries has improved by about 2x-3x in this release, according to our benchmarks. Queries that used to take ~10 minutes before this update, now only take between 3-4 minutes. We encourage you to try your own scenarios and let us know if you’re hitting these Performance levels.
  • Excel workbook import—We’ve improved latency when connecting to Excel workbooks from Power Query. You should see lower times to load previews, which translates into a more responsive experience in the Query Editor.

We continue working on Performance improvements in various areas of the product. Expect to see even more improvements coming in the next few months!

Microsoft Dynamics CRM Online connector

This month we are introducing a new connector for Dynamics CRM Online. Until now you were able to connect to Dynamics CRM Online using the OData feed connector, however, this was not very discoverable for the majority of our users.

The new Dynamics CRM connector can be found under, “From Other Sources.” With the new Dynamics CRM Online connector, users are given a sample of the Dynamics CRM Online feed URL. Users must provide a valid URL to be connected, otherwise they receive an error message.

Power Query Update 1

After specifying the correct URL, users need to provide their credentials (organizational account). Once the user is authenticated, Power Query retrieves the list of tables and entities available in Dynamics CRM. The Navigator task pane displays this list and users can select one or multiple items, edit these items or load them to the workbook—just like with any other data source in Power Query.

New transformations

Last but not least, a few improvements have been made in the Query Editor to make some transformations easier. These transformations were already possible via custom formulas, but now have been made much more usable.

  • Age and Subtract operations for Date/Time columns—When working with Date/Time columns, it’s often useful to calculate difference between two Date/Time columns (for example, order date add ship date) or to calculate the Age or Date/Time difference between a given date and “now.” These options are now available in the Date and Time menus, under the Transform and Add Column ribbon tabs.

Power Query Update 2

  • Aggregate Columns: Option to disable column name prefix—The option to disable column name prefix has been expanded to the Aggregate Columns menu. (This option was added a few months ago to Expand Columns.)  With this option, users can decide upfront whether to include a name prefix based on the original column name to the Aggregate Columns output.

Power Query Update 3

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.

Vote for what features you would like to see next in Power BI

Download this Power Query update