In conjunction with Power BI for Office 365, Power Query offers a set of capabilities for sharing and discovering queries within your organization. In this blog post, we will look into the specific details for how this works.
Sharing a query
Let’s start with a query that we created using Power Query. This query is pulling data from our Sales database and trying to correlate product sales volume with country information for each of the customers who purchased such products. We’ve spent a lot of time and effort in creating this valuable query for our analysis, and we would like to share it with the rest of the Sales team in our company.
With Power Query, we are able to share “the recipe” to cook this view, the set of data acquisition and transformation steps that connect to the data source and manipulate the data in order to turn it into a meaningful report. These steps are captured in our query using Power Query formula language (a.k.a. “M”) statements. You can see a textual representation of these steps by using the “Advanced Editor” dialog under the View tab in the Query Editor.
In order to share this query with others, we should follow these steps:
1. Sign in to Power BI for Office 365: We need to make sure that we are signed in to Power BI. We can verify this via the Sign In / Sign Out button in the Power Query ribbon tab.
2. Use Share on either the peek, the contextual menu or the contextual ribbon tab for the query that we want to share.
3. This brings up the Share Query dialog, where we can provide valuable information about the query (such as its name and description, or URL for documentation) as well as specify a set of options for how the query will be shared. These options control who will be able to find this query using Online Search, as well as whether to display a preview of the query output or not. Notice also how the information about what data source(s) the query depends on is included as part of sharing the query. In the case where the person sharing the query is part of the Data Steward role in Power BI, they will get an additional option to certify this query for others.
4. After providing all this information, we can click Share and the query will be published to our organization’s Data Catalog in Power BI.
What happens when you share a query?
The following information (metadata) gets stored in the Power BI Data Catalog in the cloud for a shared query:
- Query name and description
- Location of the data sources used by the query. For example, URL, server name/database name, and computer name/file name depending upon the type of data source. Once Power BI is aware of a data source and stores it in the Power BI Data Catalogue, we can view and manage the data sources using the Manage Data portal to annotate them with friendly names, descriptions, and access URLs. For more information, see Manage Data Source Information using the Manage Data Portal.
- Query certification status.
- Search access list for the query to determine which users/security groups can find and use this shared query.
- The URL for query documentation, if specified.
- A preview of the data output from the query, if “Include preview” was selected for the query.
- The query definition in the form of a Power Query script that specifies how to connect to the query’s data sources and transform the data. This is the script that got created when we filtered and shaped data in the query.
Finding and using this query via Online Search
All this information in the Power BI Data Catalog helps users in finding and using the shared queries. In order to find a query, we can go to Online Search and type a search term. A set of Public and Organizational results will be returned. Note that you can control the scope of your search to narrow down the results.
When you find and use a shared query in Power Query, the query definition is downloaded to the current workbook and executed in Excel to import the data referenced by the query into the workbook. Note that the user will be asked for Credentials when the query tries to connect to a data source (if they had not connected to that source using Power Query before).
Managing our shared queries
We might want to update our shared queries because some of the metadata added when we shared the query might no longer be current and relevant, or additional metadata might be available. We might also want to edit permissions to a shared query to grant access to additional users within our organization or to remove access to users that no longer require it.
We can update our shared queries to filter and shape the underlying data referenced by our shared query or just update the query metadata (settings) such as name, description, and query sharing details.
All of this can be easily done via the “Shared Queries” task pane in Power Query, which can be accessed directly from the Power Query ribbon tab. For more information, take a look at View and Update your Shared Queries.
Summary
Power Query provides a set of capabilities around data acquisition and transformation in Excel. In combination with Power BI for Office 365, Power Query helps users share queries within their organization and easily find queries shared by others using Online Search.
There is also a wide range of management capabilities for shared queries, such as the ability to modify the metadata or the query definition for your shared queries, which can be performed in Power Query.
In addition to Power Query’s built-in capabilities for Shared Queries management inside Excel, the Manage Data Portal in Power BI gives users the ability to manage data sources and queries that they have shared in Power BI, as well as understanding usage metrics for their shared queries.