View and update a shared query (Power Query)
Important This article applies to Power Query, a data analysis feature available for Excel that lets you discover, combine and refine data. You may need to enable Power Query in Excel. You can also download and install the most recent version of Power Query for Excel, which automatically enables it. Power Query technology is also built into the Power BI Designer, which is a stand-alone report authoring and data transformation tool for Power BI.
With Power Query , you can update a shared query with updated metadata added. You can also edit permissions to a shared query, to grant access to additional users within your organization, or to remove access from users.
You can update a shared query or just update the query metadata (settings) such as name, description, and query sharing permissions.
In this article
Edit Your Shared Query Definition
Each shared query includes 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, and is stored in the Power BI for Office 365 Data Catalogue when the query is shared. You can edit this query definition if you need to change how the query data is filtered and shaped. To filter and shape the data referenced by your shared query:
Sign in to Power BI from Power Query, if you are not already signed in. In Excel, on the POWER QUERY tab, select 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 view your shared queries. You cannot view your shared queries in Power Query if you are not signed in to Power BI.
On the POWER QUERY tab, select Shared.
The Shared pane displays all the queries currently shared by you. The queries are sorted by the date and time when they were shared or last modified with the most recent one at the top of the list.
In the Shared pane, point your mouse or click on a shared query name that you want to edit. A preview fly out screen for the shared query appears. Click OPEN in the preview fly out screen.
The query definition is opened in Excel, and the query is executed to import the query’s data into a new worksheet in Excel. You can then update the query definition by filtering and shaping it as required.. For more information about filtering and shaping the data in your query, see Filter, Shape, and Transform the Imported Data.
After you improve the data referenced by your shared query, on the QUERY tab, click Send to Data Catalog.
In the Send to Data Catalog update your query definition, and click Update.
Edit Your Shared Query Metadata
If you just want to edit the metadata of your shared query:
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.
On the POWER QUERY tab, click Shared.
In the Shared pane, point your mouse or click on a shared query name that you want to edit. A preview fly out screen for the shared query appears. Click EDIT SETTINGS in the preview fly out screen.
The Update Query dialog box appears with the metadata of the query that you specified while initially sharing the query. Edit the metadata information in the Update Query dialog box as required. In this case, we have changed the sharing level from specific people and security groups to all the users in the organization by selecting My organization.
To update a query to share with My organization:
In the Update Query dialog box, click My organization.
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. For more information, see Certify Queries.