We are very happy to announce the availability of a new Power Query update. You can download the update from this page.
Here is a summary of five exciting new features included in this release:
- Connect to Recent Data Sources (including pinned/favorite sources).
- Search results tabs for different Search scopes (Enterprise & Public).
- Query Editor improvements:
- Improved Insert tab capabilities & new Transform tab, which expose several new transformations.
- New “Expand Column” dialog.
- New “Choose Columns” dialog.
- Data source lineage information in Workbook Queries task pane.
- Better experience for subscribing to data services from Windows Azure Marketplace.
In the next paragraphs, we’ll show you how each of these new features work and how you can begin leveraging them. You can also let Miguel Llopis, Program Manager for our Power Query team walk you through these updates.
Recent Data Sources
One of the most commonly requested features in Power Query has been the ability to easily reconnect to a previously used data source. This is very handy as it saves users from typing in the same data source information every time. It also helps users remember data sources that they don’t connect to very often, but that they need to remember and use from time to time.
After installing this update, you will find a new “Recent Sources” button under the “Get External Data” group in the Power Query tab. This button will be enabled after you create a successful connection to any data source. From this menu you will have the option to access the 15 most recently used sources. Furthermore, you can manage the sources list via the “More Recents” option and pin up to 10 entries to the dropdown menu. Pinned entries are sorted alphabetically and most recent sources are sorted by last accessed time.
In addition to easy access to sources via the ribbon dropdown menu, there is a management dialog view where you can perform additional actions using your recent and pinned sources. You can also clear your Recent Sources list.
Search results tabs for different Search scopes
A major area of feedback from Power BI customers using Power Query Online Search was the need for better distinction between Public results and results from their own organization. As part of this update, we have changed the way search results are displayed inside the “Online Search” task pane.
Search results are now organized in tabs according to the domain they belong to (public results vs. organizational results). This allows the user to retrieve the search results for a given term from each scope all at once, without having to select a different Search scope option and perform a new search every time. In addition to better separation between Search scopes, Power Query now also provides the count of results within each scope. As part of this change we have also removed the “All” tab/scope where public results were displayed together with enterprise results.
Here is a screenshot of the updated “Online Search” task pane.
Note: the “Organization” tab will only be visible when the user is logged in. Likewise, the “Public” tab won’t be displayed if the user is connected from a restricted market (where public search is not available).
New Transformations available under Transform and Insert tabs
The number of transform operations available in the Query Editor ribbon has significantly increased in this release. The Insert tab has been enhanced to include several new options to insert new columns based on Text, Number, Date & Time and other common operations referencing values from other columns in a query.
Furthermore, a new Transform tab has been added to the ribbon. This new tab exposes common transformations that can be applied to an entire table or to specific columns within a table.
Expand builder
“Expand Column” has been available for quite some time in Power Query. However, it was not very discoverable to end users and it was not possible to modify an existing Expand step via the UI (it required manually modifying the generated formula).
In order to improve discoverability of Expand operations, we have added an entry point in the Transform ribbon tab. This option brings up a new Expand dialog which lets users search and pick the columns that they would like to expand, similar to the existing Expand dropdown menu on column headers. This dialog can also be used for editing an existing Expand step via the “Edit Settings” option (or gear icon) in an existing step.
Choose Columns dialog
To facilitate working with wide tables, we’ve introduced the “Choose Columns” dialog. This dialog can be accessed from the Home tab in the Query Editor ribbon.
“Choose Columns” allows users to search for columns by name and quickly subset a table to the relevant columns, instead of having to scroll in the preview and select several columns to keep or remove. The functionality of this transformation is equivalent to “Remove Other Columns”. In other words, Power Query keeps the columns that are selected by the user and discards any other columns that may appear in the table.
As a bonus feature, we’ve also enabled the ability to edit existing “Remove Other Columns” steps using this same dialog. As mentioned previously with “Expand Column”, having an easy way to modify existing steps was a frequent customer ask.
Data Source lineage information in Workbook Queries task pane
Query peeks in the Workbook Queries pane now also include the list of data sources that a query depends on. This enables users to easily understand where the data of a given query or report is coming from, which helps increase their level of confidence in the data retrieved by their queries.
Better experience for subscribing to data services from Windows Azure Marketplace
In order to improve discoverability of how to subscribe to Azure Marketplace services and consume them in Power Query, we have added a link in the Power Query Navigator task pane to the Azure Marketplace Catalog page for services recommended for consumption inside Excel. Users can click this link, browse services and subscribe to new ones. Then they will be able to see them in the Navigator the next time that they connect to Azure Marketplace using Power Query.
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.
Follow this links to access more resources about Power Query and Power BI: