Power Query November 2014 update—Connect to Analysis Services, Salesforce and more! 

We are happy to announce the availability of the November 2014 update for Power Query—packed with lots of new features that we hope you will enjoy and thank us for them (since we are in the 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.

Take the Power Query survey

What’s new in this update?

  • SQL Server Analysis Services connector.
  • Salesforce Reports and Objects connector.
  • Expand Columns—Ability to disable and customize column prefix.
  • Improved File menu options in the Query Editor.
  • Entry point for Advanced Editor in the Queries pane inside the Query Editor.

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 (AS) 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 first 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.

1

Salesforce Reports and Objects connector

This month we’re including the Salesforce connector as a mainstream Power Query feature, which 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; Salesforce Reports and Salesforce Objects . For a detailed description of these two capabilities, please check out this blog post.

2

Expand Columns–Disable and customize the 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 adding a checkbox in the Expand Column dropdown menu to allow users opt out of the prefix naming convention.

3

In addition to this checkbox, we’re also giving users the option to change the prefix in the Expand Columns dialog.

4

Improved File menu options in the Query Editor

We also  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 hoping to enable in next month’s update.

5

Entry point for Advanced Editor 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.

6

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.

Download the November Power Query update and don’t forget to take the Power Query survey.