Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Let's say, i have one huge table "A" that I like to use as a base table/query for 10 other queries that add their own transformations.
My understandig ist that "Reference" helps the developer to avoid having to update multiple instances of the same script. But when refreshing the dataset the engine builds one script for every query that is loaded into the model. It seems not cache the results of the queries that are referenced. That's very bad in terms of performance!
My questions:
Any help is greatly appreciated!
Solved! Go to Solution.
Hi @Sokon,
I'm afraid that's how the Power Query works in Power BI. How about the Direct Query mode?
Best Regards,
Dale
Hi @Sokon,
I'm afraid that's how the Power Query works in Power BI. How about the Direct Query mode?
Best Regards,
Dale
@Anonymous: You seem to have the same expection like I had. But, no, it's true: PQ will NOT load the base query ONCE, but as many times as there are queries that reference it +1 (itself). You can verify that fact by setting up a base query and a query that refernces it using an SQL Server database and then watch the Activity Monitor during dataset refresh: You will see TWO identical queries being run. Additionally the loading speed will plummet. It's horrible...
@v-jiascu-msft: Thanks for the confirmation that this is "as designed". I had hoped that there will be a M command that perhaps allows caching a query that I don't know. However there seems to be none and I have to look for a way around - Direct Query will certainly be a way to consider.
Edit
I found a very detailed explanation here, that helped a lot.
There IS a command Table.Buffer to cache results and that could help to solve the problem at least in some cases.
If you use query duplicate against your database, the server has to run that query 10 times. However, if you use query reference, the server only run it once, and the query would be loaded into Power BI further transformations. If you think your database server can handle 10 large queries, it would be the best option to do so since Power BI transformations can be slow with large datasets
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
71 | |
57 | |
38 | |
36 |
User | Count |
---|---|
81 | |
67 | |
61 | |
46 | |
45 |