I implemented a incremental refresh in a report that gets a lot of data from Google BigQuery. The issue is that each incremental refresh makes duplicated queries, and one of them is pretty expensive.
As Chris Webb's described in his article, power BI makes two queries in order to import data from a SQL database; one that returns a limited number of rows just to discover the table schema and another to get the actual data.
In many SQL servers, this only compromise performance, but in BigQuery, where the charge is made for the amount of data processed, regardless of the number of resulting rows, this first query is costing us a lot, because query folding is not taking place.
here I have some experiments with smaller datasets to show what is happening:
Notice that the "discovery query" above is processing 121.66 MB to return just one row, while the "data query" below, where the query folding is taking place, is processing just 7.35 MB.
I have implemented Chris solution modifying my M code, but BigQuery are still receiving duplicated queries. Is there any way to avoid the "discovery query"??
