Download Power Query Update
More flexible Load Options
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:
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 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.
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.
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.
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.
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.
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:
Thanks for the update and walkthrough - the link you posted to the download links to the June update, however...any idea when the July update will be live?
July download not live yet
@Jimmy - The July update is available now. Sorry about the confusion....
Thanks for this update. The one feature that we really need though is the ability to update more than once per day. There a re many dashboards that need to be far closer to real-time (15 to 30 minutes) and if we have to create separate dashboards for those then it's a problem. Please consider this.
Have been loving power query and puting them to good use until discovering a nsty drawback which I wish gets addressed soon. I am building a dashboard using 2 excel files. 1 is used as a data source file which pulls data from multiple sources including SharePoint. The dashboard file in turn consumes the data file using power query to bring in processed tabs to apowerpivot model. However when getting ready to deploy noticed that power query uses a physical path storage for the file. Upon editing the physical location in the advanced tab it rendered all power pivot connections from power view invalid forcing to rebuild the entire model and dashboard. A huge setback. Need a elegant way to update source file location and transparently update the referenced power pivot connections to be able to rely on power view as a dependable solution. Each release is bringing good stuff. Connections are considered foundational and fundamental so look forward to an update to manage them robustly that will help in prod deployment of refreshable solutions.
Help - how do you get to the 'load to' dialog I've looked all over for it with no sucess. Accessing it from All Properties would be way too simple!
Each time when I update/refresh my query I get a different amount of errors. After loading 17 rows it starts skipping rows and begins to count errors randomly. The data and datasource is unchanged when I repeat the query. I am using an OData feed to consume a webservice and combine data from an Azure SQL Server in the Power Query. Can you help me solving the problem please?
Comments in this blog are open and monitored for each post for a period of one week after the posting date. If you have a specific question about a blog post that is older than one week, please submit your question via our Twitter handle @MSPowerBI