ODBC and Large Data Sets

If you are on a recent release - not sure when it was added -, you can specify the clause to limit rows on previews in Query editor.

See below for an external BigQuery ODBC driver (from Google itself). This should be available for any driver.

I did test by looking at the SQL's BigQuery get:

• if you don't set this, PBI won't know what synthax to use and hence would load the whole set
• once set, PBI appended the SQL with LIMIT 1000

Should work for you too if you don't make any aggregation.

If you aggregate either in Editor or in charts, if PBI deems it's not possible to use query folding, it will load all and aggregate locally. The conditions for folding are very opaque (=undocumented).

If you are in luck, there may be a datatype with which PBI has no issue that is very similar to the "datatype-in-error".

For BigQuery, NUMERIC is problematic but FLOAT64 isn't so I can use a view that cast to float and folding works.

EDIT: I think the solution may be found somewhere here ! https://docs.microsoft.com/en-us/power-query/odbc 

by trial-n-error