Query Optimization by using previous query as source

[joshcomputer1]

I was curious if I am sourcing the same table for two different queries, should I reference the first query as my source for the second query? It is pointing to the same dataset.  Does this speed up the overall query (refresh of data)?  I have some tables that I source multiple times because the etl steps are different, but I am listing the source as the file table and not the previous query.  Will someone will experience with this chime in?

[v-juanli-msft · Accepted solution]

Hi @joshcomputer1

If i don't misunderstand you, reference the first query as my source for the other queries could improve the query performance.

You could refer to this article to get tips get Query Optimization.

https://docs.microsoft.com/en-us/power-bi/desktop-tips-and-tricks-for-creating-reports#reference-que...

if you have a base query A, and then two new queries B and C referencing A. A isn't loaded to report but B and C are.( uncheck "enable load" for queryA.

When you hit the Apply button, B and C will simultaneously start loading to report.

PowerQuery evaluations keeps a cache of data seen by evaluations on disk.

So if you are within the same cache session and pulled on A multiple times, you will essentially only pay for it the first time.

This cache will ONLY apply to raw data coming from the data source, any additional transformations will need to be performed on top of it.

Finally, since when you load queries to report, you always want the latest data. So each loading session is essentially a new cache session.

In a summary, You will only need to pay for the data coming from data source A ONCE per loading all of the queries. 

However, you will pay for the transformations on top of A N times where N = the number of queries been loaded.

Best Regards

Maggie