5 New Updates in Power Query

We are happy to announce the availability of the November 2014 Update for Power Query. This update is packed with lots of new features that we hope you will enjoy and thank us for them (hmm, since we are in Thanksgiving week…).

Before talking about new features in this update, we would like to invite you to complete a survey. It will take you only a few minutes and it will help us better understand what you like or don’t like about Power Query, how do you currently use it and what new features you would like to see in the future.

You can watch the following video or read below for more details about each feature.

SQL Server Analysis Services connector

With this update, you can now connect to Analysis Services cubes through Power Query. This connector has been long awaited for and commonly requested, and we hope that you like it. It provides exploration, transformation and import capabilities on top of AS cubes. Underneath the connection, Power Query translates user actions into MDX queries against the source. While the connector will also work for tabular instances, it is currently optimized for multi-dimensional experience. We hope to add 1st class support for DAX in the future.

You will find a new entry under the “From Database” menu that will let you connect to your AS server. After connecting, you will be able to browse your cubes and pick the desired dimensions, measures, hierarchies or KPIs from them, filter and transform the data before loading it to the worksheet or Data Model.

Salesforce Reports & Objects connector

This month we’re including the Salesforce connector as a mainstream Power Query feature. This connector has been in Preview since early October. This feature allows customers to connect to their Salesforce accounts and import data into Excel. There are two entry points offered, including Salesforce Objects and Salesforce Reports. For a detailed description of these two capabilities, please check out this blog post.

Expand Columns – Ability to disable/customize column prefix

A frequent piece of feedback from our users is that they would like to have control over the column name prefix for columns generated by an Expand Column operation. Before this update, new columns would contain the original column name as prefix and users not wanting this prefix would have to manually remove it from each column afterwards (either via Column Rename or by modifying the generated Expand Column formula).

With this update, we’re introducing a new checkbox in the Expand Column dropdown menu to let users opt out of this prefix.

In addition to this check-box, we’re also giving users the option to change the prefix via the “Expand Columns” dialog.

Improved File Menu options in the Query Editor

We have added a few more options to the Query Editor File menu. Now you can access the Data Source Settings and Options dialogs without having to leave the Editor. Note that Workbook Settings are still not accessible from the File menu. This is something that we are trying to enable in next month’s update.

Entry point for “Advanced Editor” in the Queries pane inside the Query Editor

We’ve made it much easier to access the Advanced Editor dialog when working with multiple queries in the Query Editor. Simply right click the query that you wish to edit and select “Advanced Editor” in the context menu.

That’s all for this month! We hope that you enjoy this update and find the new features valuable for you and your customers. Please send us your feedback or suggestions via Smile/Frown in Power Query.