In this blog post we want to give you an overview of the new Power Query features included as part of the Power BI for Office 365 offering. Along with several other self-service BI capabilities in Power BI, we are enhancing the Power Query experiences and feature set in Excel. You can download the latest Power Query preview add-in update and start using it today, and also sign up to use the Power BI service to enjoy a full set of self-service BI and collaboration features.
Here is a summary of the new features that you will get in this update, compared to the Power Query GA release from a few months ago:
- Online Search:
- Improved search experience, with Search result peeks (a.k.a. fly-outs) that give you more details about each of the results at a glance.
- Expanded number of available datasets, including popular datasets Open Government data from data.gov and financial data provided by Dun & Bradstreed (D&B), in addition to Wikipedia.
- New Search Tools ribbon tab, which will help you narrow down your searches based on different criteria (title, description, published date, etc.)
- Ability to cancel search operation: While performing an online search for shared queries and public data sources in Power Query, you can now cancel an in-progress search if you want to modify your search or if the search is taking more time than expected.
- “External Data” import improvements:
- Connect to a specific SQL Server / Windows Azure SQL Database: Now you can optionally specify the Database name when connecting to a server, rather than having to navigate through the entire list of databases in a server. This also unblocks those folks who don’t have broad access to multiple databases within a server. (More details)
- Native Database Query: You can import data from SQL database by directly running native database queries against the database using Power Query instead of having to build your query using Power Query interface. Read this page for more details and an example of how to use this new feature.
- Windows Azure Table Storage: You can now connect to Windows Azure Table Storage using Power Query. Find more details in this page.
- Enhanced Navigator experience which now supports up to 10,000 items per level, instead of 1,000 as it used to support before. If you still have items that don’t fit in the first 10,000, we recommend you to select a different item in the list, and replace its name in the fx bar with the name of the desired element.
- Merge multiple queries: The Merge experience has been improved to allow you to decide whether you want to keep all rows from the left table (previously, the only option) or only the rows with matches. Read more about Power Query Merge capabilities in this page.
- Enhanced Filter experience: The filter experience inside the Power Query Editor has been improved to give you the ability to load more items beyond the top 1,000. We’ve also added several new filter options for Date/Time columns. (Learn More)
- Better Integration with other Excel features:
- Better names for generated Tables in Excel sheet and Data Model (matching the query name at the time of the initial load).
- Support for renaming Excel sheet tables that are related to queries.
- Sharing of Queries with others within your organization (only available if you are signed in to your Power BI for Office 365 service from Power Query):
- You can now share a copy of your queries with others within your organization, so they can easily find them via Search. Find more details in this page.
- Access the list of your Shared Queries from the Power Query ribbon tab, to easily manage them or reuse them across workbooks.
- View and update your shared queries, or replace them with new queries as needed.
- Easily find queries shared with you by others in your organization, by using our Online Search capabilities.
In addition to new features, Power Query Preview is now available in 17 different languages: English, Spanish, French, German, Chinese (Simplified), Chinese (Traditional), Danish, Dutch, Italian, Japanese, Korean, Norwegian, Polish, Portuguese, Russian, Swedish and Turkish. If your preferred language is not in this list yet, don’t worry… We’ll be adding more languages soon!
We hope that you are as excited as we are about these new features and that you continue enjoying Power Query and sending us feedback.
- 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.