In this post
Improved Error Debugging Experience
Additional Query Editor transformations
The July 2014 Update for Power Query is now available. You can download it from this page.
This update is packed with lots of new features, so please take a look at the following video and the rest of this blog post. We hope that you like them!
Here is a summary of the new features included in this release:
- More flexible Load Options for your queries.
- Query Groups.
- Improved Error Debugging Experience.
- Additional Query Editor transformations:
- Replace Errors within a column.
- UX for defining Math operations based on a single column.
- Options dialog: Restore Defaults & Tips for Cache Management
- Update notifications: 3 notifications per update (max.), once per day at most.
More flexible Load Options
One of the most common areas of feedback about Power Query in the past has been the desire of having additional options to control how and where to load queries within your workbook. In this update, we’re introducing a new Load Options dialog to customize how to load your queries. In addition to controlling whether a query should be loaded to the worksheet or Data Model, we now offer you the option to load to an existing worksheet instead of always loading to a new worksheet. It is also more clear now how to disable the load of a query (or to “only create connection” instead of downloading the results), which until now was only possible by disabling load to worksheet and load to Data Model.
In addition to new options for how to load your queries, another area of feedback has been the need to have access to these options in all places from which users can load queries to their workbook. To address that, the following entry points have been added in this update…
•From Search results:
•From the Navigator pane:
•From the Query Editor:
•From the Workbook Queries pane and the contextual Query ribbon tab (to reconfigure the Load Options of an existing query without having to go back to the Query Editor):
Query Groups
Query Groups is a new concept introduced in this update that will help users better organize their queries within a given workbook, as well as perform bulk operations on all queries within a group (such as Refresh). Up until now, Power Query offered only a few capabilities in order to organize queries in the Workbook Queries pane, primarily moving queries up and down in the list.
With this update, users can now select multiple queries using (CTRL + Click) and move them into their custom groups. Users can define as many groups as they want in the workbook, as well as groups within groups to create more advanced organization layers. This enables them to organize and classify the queries better within their workbook. In addition, users can leverage the context menu for each group entry to apply bulk operations to all queries within that group.
Improved Error Debugging Experience
In previous Power Query updates, we introduced some transformation capabilities within the Query Editor to “Remove Rows With Errors” and to “Keep Rows With Errors” within your queries. These features were helpful in order to discard all error rows or to narrow down to just the rows with errors in the final result, but didn’t quite help users to see these errors in context, understanding the row from the result where these errors were introduced. In addition, after loading queries into the workbook, users would get in the Workbook Queries pane an indication of the total number of rows and the number of errors but weren’t able to easily preview those errors from this pane.
With this update, we’ve turned the “Number of rows with Errors” indicator into a hyperlink which brings up a preview of the rows with errors that users can explore and interact with. This preview also includes the row index to better understand where these errors appear.
Replace Errors
There are cases in which the way to resolve errors within your data is not to ignore the rows with errors but rather to replace the error values with a default value for the column. We’re introducing the ability to do this: a new “Replace Errors” operation is available in the Transform tab inside the Query Editor. This option brings up a dialog asking users for the value to replace errors with for the selected columns.
UX for defining Math operations based on a single column
Very frequently, users find the need to add new columns to their queries that reference a different column and apply a Math operation. In previous updates, we introduced the “Add Column” tab which provides several operations that will create new columns based on one or multiple existing columns. One limitation until now was that, for Math Standard operations (Add, Multiply, Subtract, Divide, etc.), users were only allowed to select two columns, representing the first and second operators.
In this update, we’ve added the ability to select just one column and use these Math operations. Users will be asked to provide the second operator in a dialog, and the result will be a new column added to the query with the selected Math calculation. This is available for all operations under the Standard dropdown menu in the “From Number” group, part of the “Add Column” tab.
Options Dialog: Restore Defaults & Tips for Cache Management options
If you followed our last two updates, you may be already aware that we have introduced new options for Custom Default Load Settings and Cache Management. This made the amount of choices in the Options dialog grow significantly, but there wasn’t an easy way to “reset” Power Query to its default behaviors. Well, now there is such way… : )
In addition to the new “Restore Defaults” button, we have added a few tooltips to help users better understand the Cache Management options introduced in our previous update.
Update Notifications Improvements
As you may be already aware, Power Query has an Update notification mechanism that tells users about our updates every month. This notification was displayed to the user in the system tray every time that they launched Excel and there was an update available. While this works out great for many users, we also heard from some of them that installing these updates wasn’t directly possible for them and they needed to notify their system administrator to perform this update and wait for a few days or weeks. At that point, seeing the Update notification continuously displayed in the system tray every time they launched Excel would become annoying…
With this update, we’ve limited the number of times that a user will see the Update notification to three times per update (i.e. three times each month). In addition, we have also limited these updates to only be displayed once per day. We believe that this will establish a good balance between making users aware of our updates while also not reminding them too many times. : )
That’s it for this update… We hope that you enjoy these new Power Query features. Please don’t hesitate to send us a smile/frown or post something in our forums with any questions or feedback about Power Query that you may have.
Follow this links to access more resources about Power Query and Power BI: