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

Download the Power BI file of demo from here:

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.