Find and Use Shared Queries (Power Query)

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

In Power Query, once queries have been shared by data stewards or other business users using Power BI for Office 365, these shared queries become available in the Power BI metadata repository. Users in the organization can find and use these shared queries (if it is shared with them) by using the online search option in Power Query to use the underlying data in the queries for their data analysis and reporting.

Important:  Shared queries may take up to five minutes, after they are shared, to be available via online search in Power Query. This is because of the search index updates, which may take up to five minutes to complete.

To find and use shared queries using Power Query:

1. Sign in to Power BI from Power Query, if you are not already signed in. 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 find and use the queries that have been shared for users within the organization. You cannot search for queries that are shared within the organization in Power Query if you are not signed in to Power BI.

2. On the POWER QUERY tab, click Online Search.

3. On clicking Online Search, the Search ribbon and the Online Search pane appear that enable you to scope and filter your query search.

   Note:  Scoping and filtering your query search is optional; it only helps you in narrowing your search to get quick results. If you do not want to use any scoping or filtering parameters, you can directly type your search keyword without any refinement parameters in the box in the Online Search pane, and press ENTER or click .

   1. Select the scope of your query search either in the Scope area of the Search ribbon or from the drop-down list in the Online Search pane. Your options are:

      • All: Search all the sources including queries shared by you, and queries shared by other users within the enterprise. This is the default selection.

      • My Shared: Search only for the queries that have been shared by you.

      • Organization: Search only for queries that have been shared within the organization using Power Query.

   2. Refine your query search based on the following parameters in the Refine area of the Search ribbon:

      • Name: Refine your search based on the query name.

      • Description: Refine your search based on the query description.

      • From: Refine your search to return results from queries shared only by the specified alias or user account.

      • Data Source: Refine your search to return results only from the specified underlying data source name.

      • Last Modified: Refine your search based on the date when the query was last modified. You can select from a pre-specified time interval in the drop-down list ranging from Today to Last Year.

      • Column Names: Refine your search to return results only from the specified column names.

      • Certified: Refine your search to return certified queries.

        When you click a refinement parameter, the Search ribbon injects the parameter keyword in the box in the Online Search pane. You can also manually type a refinement keyword in the search box, and then specify the required value for the keyword in the box in the Online Search pane.

        For example, to search for shared queries within the organization that have the word "Sample" in their name, click Organization in the Scope area, and then click Name in the Refine area of the Search ribbon. This sets the scope and injects the keyword of name:(name) to filter the results by name in the search box of the Online Search pane.

        Next, type "Sample" for the name keyword as name:(Sample), and then press ENTER or click to search for shared queries. When the search is in progress, you can click the x in the search box if you want to stop the search.

        The search results are displayed in the Online Search pane.

        

4. A history of the query searches performed by you is stored in Power Query, and you can click Recent Searches in the Search ribbon to quickly view and select one of the past query searches.

5. When you point your mouse or click the shared query name in the search results, you will see a preview of the shared query in the left pane.

   

   The preview fly out screen displays a snapshot of data referenced by the query, the columns in the dataset, last modified timestamp, the user who shared the query, and the link to the documentation, if provided for the query. You can click on the column name in the preview dialog box to jump to the respective data column in the preview.

   Note:  For queries shared within organization, the data snapshot is displayed in the preview fly out screen only if the user chose to include previews while initially sharing the query. For more information, see Share Queries.

   Additionally, the specified keyword values are highlighted in the preview of the fly out screen. For example, the word "sample" is highlighted. The keyword highlighting is available for shared query metadata (such as title and description) as well as the underlying data that is referenced by the shared query.

6. Click ADD TO WORKSHEET to import the data referenced by the shared query into the workbook as a new sheet. Alternatively, you can click FILTER & SHAPE to further improve the referenced data before importing it into the sheet.

   • If you do not have access to the underlying data sources that are referenced by the shared query, a dialog box appears that lets you request access to the data sources. In the dialog box that appears, click the Request Access link. Depending on how the access-request information is set up for the data source, you will be prompted to either send an email to an alias or request access through a URL. For more information about requesting access to a data source, see Request Access to Internal Data Sources. For information about setting up access-request information for a data source, see Manage Data Source Information in the Manage Data Portal.

   • If the underlying data in the shared query has been combined (merged or appended) using multiple data sources, you will have to specify the privacy levels before you can use the underlying combined data in the shared query. For information about privacy levels, see Privacy levels.

Related Topics

Share Queries

View and Update Your Shared Queries

View Your Shared Queries in the Manage Data Portal

Filter, Shape, and Transform the Imported Data

Understanding the Role of Data Stewards in Data Management

Microsoft Power Query for Excel Help

Data Management Experience in Power BI for Office 365 Help