Forum Discussion
Control which query go to BigQuery
The short answer is: you cannot. The longer answer is: you can set configurations in Power BI and develop your queries in a way that increase the likelihood that your data source API is only called once.
The reason is that Power Query's persistent cache takes some time to load the data from an initial query. Unfortunately, other queries that rely on the results of that query may set off their own separate processes before the persistent cache is finished storing the data. This exceprt from Chris Webb's blog explains what's happening:
Power Query’s persistent cache (which stores data on disk during a particular refresh) is updated via a background thread. And separate evaluations (i.e. separate Microsoft.Mashup.Container.*.exe processes) running at the same time are not coordinated; when evaluation A is accessing the persistent cache (including updating it), this doesn’t block evaluation B from accessing the cache. This means that even when using a shared persistent cache, PQ can potentially end up requesting the same data twice. It depends on the timing of the various requests.
Read the whole post to understand more:
The short answer is: you cannot. The longer answer is: you can set configurations in Power BI and develop your queries in a way that increase the likelihood that your data source API is only called once.
The reason is that Power Query's persistent cache takes some time to load the data from an initial query. Unfortunately, other queries that rely on the results of that query may set off their own separate processes before the persistent cache is finished storing the data. This exceprt from Chris Webb's blog explains what's happening:
Power Query’s persistent cache (which stores data on disk during a particular refresh) is updated via a background thread. And separate evaluations (i.e. separate Microsoft.Mashup.Container.*.exe processes) running at the same time are not coordinated; when evaluation A is accessing the persistent cache (including updating it), this doesn’t block evaluation B from accessing the cache. This means that even when using a shared persistent cache, PQ can potentially end up requesting the same data twice. It depends on the timing of the various requests.
Read the whole post to understand more:
If you use the ODBC connector (instead of the official Bigquery connector) it will do the same, but without the "LIMIT 1000 OFFSET 0" clause. Then, even though there are two identical requests being made to the engine, Bigquery will only charge you once since it'll run it from cache the second time.
Hope this helps!