Forum Discussion

Sokon's avatar
Sokon
Advocate V
8 years ago
Solved

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

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: Ta...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

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's avatar
Sokon
Advocate V
8 years ago

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.