We are very pleased to announce the November Update of Microsoft Power Query Preview for Excel, which you can download from this location. This is the most substantial update we’ve had in a while, and we are excited about bringing it to you – the theme on this blog a short while ago was Halloween, and we think of this as Christmas arriving early : )
The new features included in this month’s update range from new data sources to improved experiences and new capabilities for importing, reshaping, combining, managing or sharing your queries. Pretty much every product area has been improved this month, in one way or another. You can find more details below:
- Connectivity to Windows Azure Table Storage: Windows Azure Table storage is a service for storing large sets of structured, non-relational data, in Windows Azure. You can now connect to tables in this storage directly from Power Query, and build queries using the same experiences for reshaping or combining them as for any other supported source. Learn how to connect to Windows Azure Table Storage here.
- Specifying a SQL native query command: In the last update we added the ability to specify native query commands in the Power Query Formula language functions that are used for connecting to relational databases (Sql.Database, etc.). This month we’re also making it easier to author such queries, as you can now specify a command text when connecting to a database using the Power Query UI – this will be handy for users who already have SQL queries that they would like to just use for imports via Power Query. Learn more about this feature here.
- Improved navigator and multi-table import experience: In this release we have greatly improved the Import and Navigation experience for hierarchical data sources (such as Databases, Web pages, OData feeds, etc.). You can directly select a table and load it into your workbook. We’ve also added the ability to import multiple items from the same source in a single shot, and specify whether you want to land them in a new Excel sheet or in the Data Model. Note that import of relationships when building a data model is still a capability that we are working on enabling. That will be delivered in a subsequent update.
- New Transformations Ribbon: We have added a ribbon to the query editor, to improve discoverability of many transformations that were previously “hidden” behind contextual menus (right-click menus on column headers, cells, etc.). This ribbon will let you apply operations on tables and columns based on the selection in the preview. Note that everything that was available via contextual menus is still there, so you can still use them if you want.
- Ability to specify Load Settings for your query upfront: As part of the Query Editor changes, we’ve also added a Query Settings pane which lets you manage the Name and Description for your query, the applied transformation steps and also the Load Settings. The advantage of controlling this before you load the query is that you can now disable load to worksheet upfront, and this will prevent new sheets from being added to your workbook if you don’t want them. Up until now, if you disabled “Load to worksheet” for your query you would still get a placeholder on the worksheet for your query (with a message saying “Load to worksheet is disabled”), this has also been improved so we no longer create this placeholder. You might now wonder how would you be able to re-enter your query if there isn’t a placeholder for it on the sheet… Continue reading the next new feature and you will find out how you can do this going forward.
- Workbook Queries pane: A single point to manage and access all the queries in the current workbook. This pane gives you the list of all queries in the current workbook, including previews, and provides easy access to the operations that can be performed on each query: Edit Query, Share, Merge, Append, Refresh, Delete, etc. Learn more about this feature here.
- Inline Merge/Append: If you have used Power Query to merge or append different tables together, you know that you will end up with many intermediate queries and tables until you reach a final result. You can now keep adding Merge/Append steps to your query “inline”, if you do this from the Query Editor ribbon. The result will be just a new step at the end of the current query. Note that you can still get the same result as before, in a new query, if you access Merge or Append from outside the Query Editor (i.e. from the Power Query or contextual Query ribbon tab, or from the Workbook Queries side pane fly-out or contextual menus on each query).
- Certify queries that you have shared: We have added features to enable authorized business users and Data Stewards to certify shared queries in the Power BI Data Catalog as being authorized or authoritative:
- Data Steward role: Power BI administrators can use the Admin Center to add users to the Data Steward role. Only members of this role can certify their shared queries.
- Certify shared queries: Members of the Data Steward role can apply the “Certified” flag to queries when sharing them, and can set or clear the flag for shared queries when editing the shared query metadata in Power Query.
- View certification status in Online Search: Certified queries will be decorated with a “ribbon” in the Power Query Online Search results side pane, and in the preview fly-out.
You can learn more about Certifying Shared Queries in here.
- Added support for 20 additional languages in this release (37 in total): In addition to the 17 languages that were already supported (see the list at the end of our previous update announcement here), we have added support this month for Bulgarian, Croatian, Czech, Estonian, Finnish, Greek, Hindi, Hungarian, Indonesian, Kazakh, Latvian, Lithuanian, Malay (Malaysia), Portuguese (Portugal), Romanian, Serbian (Latin), Slovak, Slovenian, Thai and Vietnamese.
This is all for now… As you can see we are working hard to improve Power Query every month and we really value your feedback about the experiences and capabilities in the product. Please don’t hesitate to reach out to us via Send Smile/Frown or using our forum with any questions, issues or suggestions that you may have.
Next steps:
- Install Power Query Preview for Excel today.
- Access Power Query Documentation and samples.
- Sign up for Power BI Preview.
- Follow the Power BI Getting Started guide.
- Visit the Power Query Forum.
- Show of your own Power BI skills and enter the Power BI Demo Contest.
Enjoy!