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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
You can easily view and copy error details inside the Filter Column menu. This is very useful to troubleshoot errors while retrieving filter values.
You can remove items directly from the Selection Well instead of having to find the original item in the Navigator tree to deselect it.
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:
Does this application work with Microsoft Dynamics CRM and Microsoft Dynamics GP Data? If so, can anyone point me in the right direction? e.g. Should I look for information pertaining to CRM and Power BI inside the CRM application's support area? Thanks in advance for your help - this stuff is great!
Please stop sending the update. send an email to me and I can forward to my IT department to implement.
Will Power Query support data Editing ?
Way to go! I prided myself in my VBA skills - but with this I rarely have to mess around with that messy outdated language anymore. Programming complicated processes is a snap and I get it done in way less time with the 'Functional Programming' tools this add-in is provides. Excel was way past due to support a new programming paradigm. (Side note - DO NOT KILL THE EXPRESSION.EVALUATE('text' , #SHARED) "bug" - evaluating expressions stored as text is an amazing functionality!