Error with data loading from Big Query View

[wicker]

Hi everyone!

Trying to load data from Big Query's view, which get one of the tables from Google Spreadsheets. In BQ view running is correct, but in BI I get error "DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Error while reading table: datalake-257923.data_schemas.managers, error message: Failed to read the spreadsheet. Errors: No OAuth token with Google Drive scope was found". 

Query in BI below

[FabricU10]

Hey, what solved it for me was disabling the query folding option;

either set to false [EnableFolding=false]

or just call 

Value.NativeQuery(GoogleBigQuery.Database(){[Name="<GBQ project name>"]}[Data], "<Query>")

without the parameter.

Hope this helps.

Best,

[v-shex-msft · Accepted solution]

HI @wicker,

It seems like a compatibility issue with power bi bigquery connector and backend verification of google bigquery api.
I'd like to suggest report this to power bi or google bigquery support to check backend reasons. (notice: attach your power bi desktop version to help them to troubleshoot)

submit a support ticket

In addition, you can also try to use web connector to manually invoke bigquery rest API with valid OAuth tokens, it should prevent this issue.

Regards,

Xiaoxin Sheng

[amitchandak]

@wicker , a related article but not with power bi

https://stackoverflow.com/questions/49807276/bigquery-no-oauth-token-with-google-drive-scope-was-fou...