DataSource.Error: ADBC: Dataset XXX was not found in location US

[mhafizzul]

Hi,

I was trying to add new Data Source to my Power BI file, using Google BigQuery Connector.

After logging in using my Google account, I am able to see the Google project that I want to access from the wizard. I am also able to expand the project to see the list of datasets under the project. But once I click on the dataset. I got error (Refer below screenshot)

The full error as per below:

DataSource.Error: ADBC: Cannot execute <ExecuteQueryInternalAsync>b__1 after 5 tries. Last exception: The service bigquery has thrown an exception. No HttpStatusCode was specified. Job mxs-ent-report-prod/US/job_40167107_6aca_4a35_95c3_4b7b0e11f057 contained 2 error(s). First error message: Not found: Dataset xxx_project_name_xxx:AP_Final was not found in location US
Details:
    DataSourceKind=GoogleBigQuery
    DataSourcePath=GoogleBigQuery
    AdbcStatus=1
    SQLState=
    NativeError=0

The thing is, this used to work before, I never get error saying that the dataset not found in location US. But when today I tried to refresh the existing pbix file, I get this error, and also when I try to create new pbix file and create new data source, I get the same error.

This happen to all the dataset under the same project.

I think this is a bug in Power BI, and it just happen. It work nicely last week

Kindly help!

[tayloramy · Accepted solution]

Hi @mhafizzul, 

According to google's docs: 

https://cloud.google.com/bigquery/docs/troubleshoot-queries#location_not_foundThis error returns whe...

To address this issue, specify the location of the dataset in the query or confirm that the dataset is available in the same location.

You might be able to explicitly specify the location of the dataset in the connection. 

There's also been other community posts about similar errors indicating some regression in the service: https://community.fabric.microsoft.com/t5/Service/Power-BI-Service-BigQuery-connector-failing-with/t...

There is an active known issue for Google BigQuery right now: https://support.fabric.microsoft.com/known-issues/?product=Power%2520BI&active=true&fixed=true&sort=...

Can you try reverting to the old implementation of the connector and see if that works? 

In Power BI Desktop go to File -> Options and settings -> Options -> Preview features and turn off “Use new Google BigQuery connector implementation,” then restart Desktop and reconnect. If your M query shows GoogleBigQuery.Database([Implementation="2.0"]), remove that flag. https://learn.microsoft.com/en-us/power-query/connectors/google-bigquery

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[tayloramy · Accepted solution]

Hi @mhafizzul, 

According to google's docs: 

https://cloud.google.com/bigquery/docs/troubleshoot-queries#location_not_foundThis error returns whe...

To address this issue, specify the location of the dataset in the query or confirm that the dataset is available in the same location.

You might be able to explicitly specify the location of the dataset in the connection. 

There's also been other community posts about similar errors indicating some regression in the service: https://community.fabric.microsoft.com/t5/Service/Power-BI-Service-BigQuery-connector-failing-with/t...

There is an active known issue for Google BigQuery right now: https://support.fabric.microsoft.com/known-issues/?product=Power%2520BI&active=true&fixed=true&sort=...

Can you try reverting to the old implementation of the connector and see if that works? 

In Power BI Desktop go to File -> Options and settings -> Options -> Preview features and turn off “Use new Google BigQuery connector implementation,” then restart Desktop and reconnect. If your M query shows GoogleBigQuery.Database([Implementation="2.0"]), remove that flag. https://learn.microsoft.com/en-us/power-query/connectors/google-bigquery

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[mhafizzul · Accepted solution]

Hi @tayloramy ,

I manage to solve this by:

1. Following your suggestion to reverting to old implementation (File -> Options and settings -> Options -> Preview features and turn off “Use new Google BigQuery connector implementation,” then restart Desktop and reconnect. If your M query shows GoogleBigQuery.Database([Implementation="2.0"]), remove that flag)
2. Setting the Billing Project Name. To get the billing project name, I go to https://console.cloud.google.com/iam-admin/settings , and take the Project ID specified there

Thanks!

[tayloramy]

Hi @mhafizzul,

See this similar post: https://community.fabric.microsoft.com/t5/Power-Query/DataSource-Error-ADBC-UnknownError-while-runni...

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution. 

[mhafizzul]

Hi @tayloramy ,

I have went through the post, and see one of the step mentioned by one of the replier:

When connecting, set the Billing Project ID and verify that your datasets are in the same region (US/EU). 

But my dataset is in region asia-southeast1. Is that the cause (and again, it work last week)

All my datasets are in region asia-southeast1. Can that be the reason? But it work before ...