In this Post
Download the Power Query Update
Support for loading Binary-typed columns (such as images) to the Data Model
Multi-query editing in the Query Editor
Automatic type coercion for Text operations over any scalar column type (number, date, etc.)
Query Properties dialog for editing name & description
Simplified “Share Query” experience
“Enable Tracing” option in the Options dialog
We are very glad to announce the availability of a new Power Query update. You can get this update, as always, from our official download page.
This update is packed with new features and usability improvements to existing features. Here is a quick summary of what has been added or improved in this update:
- Support for loading Binary-typed columns (such as images) to the Data Model
- Multi-query editing in the Query Editor
- Pivot Columns
- Automatic type coercion for Text operations over any scalar column type (number, date, etc.)
- Query Properties dialog for editing name & description
- Simplified “Share Query” experience
- “Enable Tracing” option in the Options dialog
You can watch the following video or continue reading this e-mail for more details about each feature.
Support for loading Binary-typed columns (such as images) to the Data Model
With this update, we’ve enabled the ability to load Binary-typed columns into the Data Model from Power Query. This lets users download blobs of data into the Data Model from data sources that support this data type (such as SQL Server) and also lets users leverage existing Power Query library functions (such as File.Contents and Web.Contents) to load and store contents from an specified location to the Data Model.
Note that, after loading a table containing a Binary column from Power Query into the Data Model, you will need to go into the PowerPivot window and perform the following two steps:
- Open the Table Properties dialog from the Design tab in PowerPivot and enable the column containing Binary data.
- Customize the Table Behavior in the Advanced tab, to indicate that your Binary data column is the Default Image.
After performing these two steps you will be able to visualize the images in a Power View report.
Multi-query editing in the Query Editor
We have added a Navigator pane to the left side of the Query Editor. This Navigator allows users to quickly switch between queries and perform changes to each of them without having to close the Query Editor and open a different query from the Workbook Queries task pane. The Navigator pane reflects the list of queries and query groups in the current workbook, just like the Workbook Queries task pane does.
Pivot Columns
Before this update, users were able to perform Pivot Columns operations by typing in the corresponding library function for it (Table.Pivot). However, this was not very discoverable and it was also hard due to the number of parameters required by that function. With this update, we have added 1st class UX support for Pivot Columns. This operation can now be accessed via the Transform tab in the Query Editor.
After selecting the desired column to pivot on and clicking Pivot Column, users are presented with a few options to customize the Values Column to use (column that contains the cell values for the new columns), the Aggregation function to apply to these values. There is also a link to a Help page with more information and examples about pivoting columns in Power Query.
Automatic type coercion for Text operations over any scalar column type (number, date, etc.)
With the goal of making it easier to apply column-level transformations, we have enabled in this update the ability to perform Text transformations (i.e. split/combine columns, uppercase/lowercase, etc.) on top of any scalar-type column such as numeric (Whole or Decimal Numbers) or date & time columns. When applying such operations to transform an existing column or insert a new columns based on existing ones, Power Query will take care of automatically handling column type conversions so that the Text operations behave as expected. This reduces the need to explicitly change column types in preparation for additional column transformations.
Query Properties dialog for editing name & description
One very common piece of feedback from our customers was that having to reopen the Query Editor in order to change the name or the description of an existing query was very inconvenient. In this update, we have added a new Properties dialog to control the name & description of each query and made it accessible from the Query ribbon tab and the Workbook Queries task pane.
Note that the Properties dialog is also accessible from the Home tab inside the Query Editor and that the Description field has been removed from the Query Settings pane. This allows for additional space under the Applied Steps section.
Simplified “Share Query” experience
Another area of feedback has been that the “Share Query” dialog was a little bit overwhelming due to the amount of fields presented to the user at once: name, description, data sources list, documentation URL, preview settings, etc. In addition to this, it wasn’t quite clear to the user what was the relationship between a shared query and a local copy of the query.
With this update, we’re changing the terminology around “Share Query” to be more descriptive about the product behavior. Users can send a point-in-time copy of the query to the Power BI data catalog and, optionally, share it with an specific set of users and groups of users or with their entire organization. We have also divided the input fields in the dialog between two tabs: Query (properties related to the query being sent to the catalog) and sharing (options for who should be able to find the query in the Power BI Data Catalog).
Entry point for managing your Power BI Data Catalog queries is now available under the Power BI group in the Power Query ribbon tab, right next to the Sign In/Out button.
Note that if you are using the Documentation URL field when sharing queries, this field is not available in this latest update. This is a temporary issue that will be addressed in next month’s update, by bringing this field back into the “Send to Data Catalog” dialog. In the meantime, please use the previous Power Query bits (32-bit, 64-bit). We apologize in advance for the inconvenience that this issue might cause to you.
“Enable Tracing” option in the Options dialog
If you have ever had to send us some traces to help us troubleshoot an issue with Power Query, you already know how painful the process to enable tracing was… until now. It involved manually modifying a Windows Registry key, reproducing the scenario to generate traces and, finally, disabling tracing via the Windows Registry so that these traces wouldn’t continue being generated (taking up a significant amount of local disk space).
With this update we have added a new option under the Options dialog to easily turn on tracing. We will also take care of automatically disabling tracing after all in-flight Excel sessions are closed, to avoid taking up too much local disk space.
That’s all for this update… We hope that you enjoy these features and continue sending us your valuable feedback and suggestions, so that we can continue improving Power Query with every new update.