Using "Reference" in Query Editor does not prevent loading base query multiple times

[Sokon]

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.

• "Duplicating" the A query obviously ist the wrong desicion: Table A would be loaded multiple (10+1) times.
• Knowing that I used "Reference", expecting Power BI to load A once and then take that data and continue with the 10 other queries on top of that. But: Like before, the base table seems to be loaded 11 times.

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:

• Is my understanding correct?
• Is there a way (a M command perhaps?) to tell the engine to cache/persist a certain query so we can avoid to load tables multiple times?

Any help is greatly appreciated!

[v-jiascu-msft · Accepted 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

[Sokon]

@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.

[Anonymous]

@Sokon,

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