Skip to main content

Blog consacré à Microsoft Power BI

6 New Updates in Power Query

Headshot of article author Miguel Llopis

Group Program Manager - Data Integration
13 janvier, 2015

Try what’s coming next for Power BI!

We hope all of you are enjoying 2015 so far! The Power Query team has been busy working on the latest Power Query update, which includes a new connector (or many new connectors, in this case!) and a bunch of other usability improvements.

You can download this update from the Power Query download page.

In this update

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

  1. ODBC Connector
  2. “From Azure” ribbon dropdown menu
  3. Workbook Settings dialog
  4. Option to enable or disable relationship detection
  5. Column & Row counts in Query Editor Preview
  6. Confirmation dialog to delete a query when deleting a worksheet

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

 

ODBC Connector

This new connector can be found under “From Other Sources > From ODBC” and it allows users to connect to their generic ODBC providers. This makes it possible for users to bring in data via Power Query from several data sources that are not natively supported. Users can connect and import data from ODBC-based data sources by specifying the connection string parameters and a SQL statement to execute.

 

Note that credentials should still be entered on the credential page, rather than in the Connection String field above, to ensure credentials are not inadvertently shared with the query. Similar to other SQL Statement connections, Power Query will not be able to optimize query steps after the SQL statement so it is suggested that you optimize the original statement as much as possible (i.e. include “group by”, “join” operations, etc.)

“From Azure” ribbon dropdown menu

With the addition of several new data sources in the past few months, we started to hit issues related to the amount of entries under the “From Other Sources” dropdown menu. We considered sending bigger, higher-resolution monitors to all Power Query users as a gift, so everyone could see all entries in this menu. However, given the large amount of Power Query customers, this was not a viable solution. As a fallback plan, we have decided to create a new top-level menu for Azure-based data sources to balance the number of entries in each dropdown menu. This does not add any new data sources, but improves navigation, discoverability and ease of access for our data sources lists in the ribbon.

Workbook Settings dialog

In this update, we’re modifying the way in which Workbook Settings are exposed in the Power Query ribbon. Instead of having Workbook Locale and Fast Combine directly in the ribbon, we’ve moved them into a new Workbook Settings dialog. This allows us to provide better descriptions about these two settings, as well as make room for a new setting that we are adding this month: Enable/Disable Relationship Detection.

Option to enable or disable relationship detection

When loading queries into the Data Model, Power Query will try to detect relationships between them. These relationships might exist in the data source (i.e. SQL Server) or might be implicitly created by the user as part of their query steps. For instance, merging two queries would make Power Query detect a relationship between these two queries, which would be automatically created in the Data Model when these queries are loaded.

As much as this is a useful feature, it is also one that may cause an increase in the load time for your queries. In many cases, users either do not want these relationships or know that there aren’t any relationships beforehand, which make this additional wait time unnecessary. Based on feedback from many of you, we have decided to add an option to turn this relationship detection off. Note that this setting is workbook-specific, so you will need to modify the default behavior (default is to continue trying to detect relationships) for each workbook via the Workbook Settings dialog.

Column & Row counts in Query Editor Preview

We have added counts for number of columns and rows in the Query Editor Preview. This will help you get a better sense for the shape and size of your data while defining transformations in your queries. Note that these counts are based on the Query Editor Preview, not the full data set. In particular, the number of rows will be smaller than the actual table row count in most cases. You will see these numbers change as more or less rows are displayed in the preview (for instance, you will see the row count increase when scrolling down in the preview).

Confirmation dialog to delete a query when deleting a worksheet

Before this update, deleting an Excel worksheet would result in all queries that had been loaded to this worksheet being automatically deleted as well. This caused accidental data loss for some users, unaware of this default behavior. Based on feedback, we have decided to add an additional confirmation for what to do with queries when the Excel worksheet that they were loaded to is deleted. There are two choices for the user: delete queries or modify their load settings to disable load to worksheet.

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 Power Query from our official download page.