New updates for Power Query

New updates for Power Query

Rate This
  • Comments 19

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:

Leave a Comment
  • Please add 1 and 8 and type the answer here:
  • Post
  • Thank You so much for Preserve Custom Columns feature! You and Power Query are great!

  • Is there currently any plans to allow for power queries to be refreshed in Power BI via the scheduler?

  • It will be better if it supports previous IEs, not only IE 9

  • Thanks for preserving custom columns! In past I was not using it due to that!!

  • God Bless you!

    I was just sending updates through office feedback and the partner website about this tools functionality.

  • I do ask one thing. For better feedback about Office Products, you need to allow the feedback tool to collect the open windows too. I have to use prtsc/paint and then reference them in the feedback now.

  • With all the new functionality and the versatility that the new BI tools represent, I'm finding an explosion of the number of tabs I'm creating. I think it would be a great idea to allow tabs to be nested so the user could keep a very orderly workspace and the naming of things might be  little easier and more like metadata.

    Thanks for all the hard work!!

  • THANK YOU!  These are some of the things that I have been waiting for!  I do second the fact that I have so many worksheet tabs... I resorted to a TOC sheet and number the other tabs with a number WITHOUT the preceeding "Sheet" label... it works... good way to give a high level overview

  • In office 365 there are power query and power pivot?

  • "◦Preserve data in the Data Model when you modify the Load to Worksheet setting of a query that is loaded to the Data Model"

    This is the best functionality to come for me so far and just in time. I have had to rebuild entire data model as I only tried to change my 'load setting' to the data model by adding a new column and then unchecking 'load to data model' and all the relationship and measures in my model disappeared. It was a real pain to rebuild everything from the scratch but I just can't be angry with Power Query and the team because I know that, among the self-service BI tool teams, they've been the best in terms of updating and upgrading and that only has to come from listening to the users' suggestions and query..................

    WELL DONE POWER QUERY TEAM.  KUDOS!

  • Great!

    Preserve Custom Columns will help a lot. You're pretty fast in updating the software.

    //Jörgen

  • A very good update. I love the news about preserving columns, formatting and previous queries data.

    Now if only it was possible to have Google Analytics as a built-in data source, I'd be completely happy :)

  • Tables from other workbooks!!!!!  Thank you!!!

  • A very good update. However, the following two improvements are hardly understandable:

    - Preserve data in the Data Model when you modify the Load to Worksheet setting of a query that is loaded to  the Data Model

    - Ability to view and copy errors in the Filter Column dropdown menu

    Maybe you could provide further explanations or screenshots.

  • make this update page go away, please.

Page 1 of 2 (19 items) 12