Power Query is the component of getting data in Power BI. But have you used Power Query to get metadata of the Power BI queries itself? In this post I’ll show you a quick and simple way of using Power Query to get metadata (name of queries and the data in queries) from all queries in Power BI. I have previously explained how to use #shared keyword to get list of all functions in Power Query, this post shows how to use #shared or #sections to get all queries (and parameters, and functions, and lists… ) from Power BI. If you want to learn more about Power BI; read Power BI online book from Rookie to Rock Star.
* Thanks to Alex Arvidsson who brought this question, and was the cause of writing this blog post.
Question: How to Fetch Name of All Queries into One Query?
Consider below Power BI file, that has functions, parameters, and queries. Queries also returns tables, values, and lists;
The question is how I can get list of all these queries and their values as a new query? Let’s see the answer
Answer: Using #shared or #sections Keywords
#shared to Get Current File’s Queries Plus All Functions
I have previously explained what #shared keyword does; it is a keyword that returns list of all functions, and enumerators in Power Query. It can be used as a document library in the Power Query itself. It will also fetch all queries in the existing Power BI file. Here is how you can use it:
Create a New Source, from Blank Query.
Then go to Advanced Editor of that query (from Home tab or View tab)
write only one keyword in the Advanced Editor: #shared
And Click on Done. Result will comes up quickly;
You can see in the result that #shared fetch all existing queries, plus all built-in functions in Power Query. the section which is marked in above result set in where you can find queries from the current file. Note that the Query1 itself (which includes the #shared keyword) is listed there. the limitation of this method is that it won’t return your custom function; “my function” in this example. the next method however would pick that as well.
#sections To Get Current File’s Queries
The other way of fetching list of queries is using #sections keyword. #sections keyword will give you list of all sections in Power Query (This post isn’t right place to explain what sections are, but for now, just consider every query here as a section). so same method this time using #sections will return result below;
Result is a record, that you can simply click on the Record to see what are columns in there. Columns are queries in the current file:
This method also returns functions in the current file, which previous method with #shared didn’t. So this is a better method if you are interested to fetch function’s names as well.
The result is a record which can be converted to a table (this gives you better filtering options in the GUI). You can fine the Convert Into Table under Record Tools Convert Section menu.
After having this as a table, then you can apply any filters you want;
Sample Scenario of Usage
There are many usages of getting name of queries in another query. One sample usage of that can be getting different queries coming from different places, and the only way to identify the source is query name. In this case instead of manually adding a column to each query and then combining them together, you can use this method to get the query name dynamically. In screenshot below ProductA, ProductB, and ProductC are coming as source queries, and you can simply do a filtering to get them all with their product names.
And you can expand it to tables underneath if you want to (this would work if they all have same data structure);
and as a result you have combined result of all queries, with the query name as another column;
Download the Power BI file of demo from here:
6 thoughts on “Get List of Queries in Power BI”
Seems #shared cannot be refreshed in Power BI service.
Yes, #shared is an environment variable let’s say, and it is not like other variables to work as an auto refresh in the website. The main usage of that is in Power Query on your machine.
I am not sure why my table is empty after clicking “Close & Apply”. The headers are there, but there is nothing.
This method only works inside the Power Query Editor. the environments of #shared and #sessions are not available when you load data into Power BI.
Oh Reza, that’s such a shame! I’ve been thinking what’s wrong with my code for days. Can you please share if there are ways that I could dynamically append my queries into a table that is available when I load the data into Power BI?
Unfortunately, there is no way to append query names like that when you load data into Power BI unless you statically have their names somewhere.
You can, however, append your queries data into one if you want with a simple append transformation.