[Power BI Preview Only]
After you have connected to the required data sources and improved (filtered, shaped, and transformed) the data to match your requirements, you can share the metadata of the resultant dataset as a query in Power Query with all users or a specific set of users within the organization. Shared queries appear in search results when users perform an online search in Power Query. This enables the users to easily discover and reuse the shared queries and their underlying data for their data analysis and reporting.
Important Microsoft Active Directory supports two types of groups: Distribution Groups and Security Groups. Distribution Groups are used only for email applications, to enable sending emails to groups of users. Security Groups are used to provide access to shared network resources. Security Groups may be mail-enabled, allowing them to be used as email recipients, but Distribution Groups cannot be used to provide resource access. For more information, see Active Directory Group Types. Using Power Query, you can share queries only with Security Groups, and not with Distribution Groups. If you cannot find the group with which you want to share a query, please contact your network administrator.
In this article
Sharing a Query
To share the data set as a query:
- Sign in to Power BI. In Excel, on the POWER QUERY tab, click Sign In. In the Sign In dialog box, click Sign In, and then enter your organizational account information that was used for signing up for Power BI.
Important Signing in to Power BI using your organizational account enables you to share queries within your organization and also to discover and use queries shared by other users within your organization. You cannot share queries or find and use queries that other users have shared, if you are not signed in to Power BI.
- After signing in, on the QUERY tab in Excel, click the down arrow on the Share icon, and then click Share.
- In the Share Query dialog box:
- Specify a name and description for your query.
Tip You can provide a concise and meaningful name and a description that explains how users should use the query and its data. The description should include enough information to enable information workers to choose the right query, and understand how and where it should be used, in the context of the business processes to which the data relates, not in the context of the system or systems from which the data is taken. Also, remember that the name and description are added to the cloud search index for use in Power Query online searches, so a complete description is valuable in multiple ways.
- The Share Query dialog box displays information about the underlying data sources for the query you are sharing. The number next to the Data Sources field in the brackets signifies the number of data sources from which the query gets its data. If a friendly name of the underlying data source is available, it is displayed. Otherwise, the path to the data source is displayed.
- For single underlying data source, you can either click on the View in portal link or the data source listing to view and manage the data source in the Manage Data portal. For more information, see View Data Sources in the Manage Data Portal.
- For multiple underlying data sources, click the View in Portal link to view and manage all the underlying data source(s) for the query in the Manage Data portal or click a data source listing to view and manage it individually in the Manage Data portal. For more information, see View Data Sources in the Manage Data Portal.
- To certify the query, select the Certify this query for others check box. For more information about certifying queries, see Certify Queries.
Note The Certify this query for others check box is visible only if your user account is a member of the data steward group role in Power BI. Your Power BI Administrator can add a user to the data steward group role using Power BI Admin Center. Contact your administrator to get your user account added to the data steward group role if you need to certify queries.
- Under the Share with area, specify the users with whom you want to share the query within your organization. Clicking Just me does not share the query with anyone except you. Clicking My organization shares the query with all the users within your organization who have access to Power BI. Clicking People and security groups enables you to specify the specific users or security groups within your organization with which you want to share the query. As you type the name of the user or a security group to share your query with, a drop-down list appears matching the user or security group names in your organization from where you can select the required user or security group account.
Note The user account that shares a query will by default have access to the query even if his/her account is not explicitly specified when you select People and security groups option.
- To include support documentation for your query, specify the URL of the documentation in the Documentation URL field. This is an optional field, and you can skip it.
Tip Consider creating a Word document for your shared query if a lengthier description is required than what is provided earlier. Save the document to a shared location such as a SharePoint document library, and paste the URL to the shared document into the Documentation URL field when sharing the query
- To include a preview of the top rows when others search for the query, ensure that the Previews check box is selected.
Caution If you enable preview for your shared query, the data referenced by the query is visible to users in the preview fly out even if they do not have access to the data, although they cannot use the query to import data unless they have access to it. You might want to disable previews for your query if it contains references to sensitive data to prevent unauthorized users from viewing partial dataset in the preview.
- Click Share a Copy.
Important When you share a query, it may take up to five minutes for your shared query to be available via online search. Your shared query will be immediately available to you in the Shared pane in Power Query, but will not appear in search results until the search index is updated, which may take up to five minutes to complete. For information about viewing your shared queries in the Shared pane, see View and Update Your Shared Queries.
Top of Page
What happens when you share a query?
The following information (metadata) gets stored in the Power BI Data Catalogue 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, you 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 referenced by the query, if data 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 script is created when you filter and shape data in your query.
This information in the Power BI Data Catalogue helps users in finding and using the shared queries. When you use a shared query in Power Query, the query definition is executed in Excel to import the data referenced by the query into a new worksheet in Excel. To find and consume shared queries using online search in Power Query, see Find and Use Shared Queries.
Top of Page
View Your Shared Queries in the Manage Data Portal
View and Update Your Shared Queries
Replace Your Existing Shared Queries
Delete Your Shared Queries
View and Manage Queries in a Workbook
View Usage Analytics for Your Shared Queries from Power Query
Understanding the Role of Data Stewards in Data Management
Data Management Experience in Power BI for Office 365 Help