The Power Query team has been busy adding a number of exciting new features to Power Query. You can download the update from this page.
New features for Power Query include the following, please read the rest of this blog post for specific details for each.
- New Data Sources
- Updated “Preview” functionality of the SAP BusinessObjects BI Universe connectivity
- Access tables and named ranges in a workbook
- Improvements to Query Load Settings
- Customizable Defaults for Load Settings in the Options dialog
- Automatic suggestion to load a query to the Data Model when it goes beyond the worksheet limit
- Preserve data in the Data Model when you modify the Load to Worksheet setting of a query that is loaded to the Data Model
- Improvements to Query Refresh behaviors in Excel
- Preserve Custom Columns, Conditional Formatting and other customizations of worksheet tables
- Preserve results from a previous query refresh when a new refresh attempt fails
- New Transformations available in the Query Editor
- Remove bottom rows
- Fill up
- New statistic operations in the Insert tab
- Other Usability Improvements
- Ability to reorder queries in the Workbook Queries pane
- More discoverable way to cancel a preview refresh in the Query Editor
- Keyboard support for navigation and rename in the Steps pane
- Ability to view and copy errors in the Filter Column dropdown menu
- Remove items directly from the Selection Well in the Navigator
- Send a Frown for Service errors
Connect to SAP BusinessObjects BI Universe (Preview)
This connectivity has been a separate Preview feature for the last month or so. In this release, we are incorporating the SAP BusinessObjects BI Universe connector Preview capabilities as part of the main Power Query download for ease of access. With Microsoft Power Query for Excel, you can easily connect to an SAP BusinessObjects BI Universe to explore and analyze data across your enterprise.
Access tables and named ranges in an Excel workbook
With From Excel Workbook, you can now connect to tables and named ranges in your external workbook sheets. This simplifies the process of selecting useful data from an external workbook, which used to be limited to sheets and users had to “manually” scrape the data (using Query transform operations).
Customizable Defaults for Load Settings in the Options dialog
You can override the Power Query default Load Settings in the Options dialog. This will set the default Load Settings behavior for new queries in areas where Load Settings are not exposed directly to the user, such as in Online Search results and the Navigator task pane in single-table import mode. In addition, this will set the default state for Load Settings where these settings are available including the Query Editor, and Navigator in multi-table import mode.
Preserve Custom Columns, Conditional Formatting and other customizations of worksheet tables
With this Power Query Update, Custom Columns, conditional formatting in Excel, and other customizations of worksheet tables are preserved after you refresh a query. Power Query will preserve worksheet customizations such as Data Bars, Color Scales, Icon Sets or other value-based rules across refresh operations and after query edits.
Preserve results from a previous query refresh when a new refresh attempt fails
After a refresh fails, Power Query will now preserve the previous query results. This allows you to work with slightly older data in the worksheet or Data Model and lets you refresh the query results after fixing the cause of errors.
Automatic suggestion to load a query to the Data Model when it goes beyond the worksheet limits
When you are working with large volumes of data in your workbook, you could reach the limits of Excel’s worksheet size. When this occurs, Power Query will automatically recommend to load your query results to the Data Model. The Data Model can store very large data sets.
Preserve data in the Data Model when modifying the Load to Worksheet setting of a query that is loaded to the Data Model
With Power Query, data and annotations on the Data Model are preserved when modifying the Load to Worksheet setting of a query. Previously, Power Query would reset the query results in both the worksheet and the Data Model when modifying either one of the two load settings.
Remove Bottom Rows
A very common scenario, especially when importing data from the Web and other semi-structured sources, is having to remove the last few rows of data because the contents do not belong to the data set. For instance, it’s common to remove links to previous/next pages or comments. Previously, this was possible only by using a composition of custom formulas in Power Query. This transformation is now much easier by adding a library function called Table.RemoveLastN(), and a button for this transformation in the Home tab of the Query Editor ribbon.
Fill Up
Power Query already supports the ability to fill down values in a column to neighboring empty cells. Starting with this update, you can now fill values up within a column as well. This new transformation is available as a new library function called Table.FillUp(), and a button on the Home tab of the Query Editor ribbon.
New Statistics operations in the Insert tab
The Insert tab provides various ways to insert new columns in queries, based on custom formulas or by deriving values based on other columns. You can now apply Statistics operations based on values from different columns, row by row, in their table.
Ability to reorder queries in the Workbook Queries pane
With the latest Power Query update, you can move queries up or down in the Workbook Queries pane. You can right-click on a query and select Move Up or Move Down to reorder queries.
More discoverable way of cancelling refresh of a preview in the Query Editor
The Cancel option is now much more discoverable inside the Query Editor dialog. In addition to the Refresh dropdown menu in the ribbon, this option can now be found in the status bar at the bottom right corner of the Query Editor, next to the download status information.
Keyboard support for navigation and rename in the Steps pane
You can now use the Up/Down Arrow keys to navigate between steps in your query. Also, press the F2 key to rename the current step.
Ability to view and copy errors in the Filter Column dropdown menu
You can easily view and copy error details inside the Filter Column menu. This is very useful to troubleshoot errors while retrieving filter values.
Remove items directly from the Selection Well in the Navigator
You can remove items directly from the Selection Well instead of having to find the original item in the Navigator tree to deselect it.
Send a Frown for Service errors
We try as hard as possible to improve the quality of Power Query and all of its features. Even then, there are cases in which errors can happen. You can now send a frown directly from experiences where a service error happened, for instance, an error retrieving a Search result preview or downloading a query from the Data Catalog. This will give us enough information about the service request that failed and the client state to troubleshoot the issue.
That’s all for this update! We hope that you enjoy these new Power Query features. Please don’t hesitate to contact us via the Power Query Forum or send us a smile/frown with any questions or feedback that you may have.
You can also follow these links to access more resources about Power Query and Power BI: