Power BI Desktop fails when applying data source updates

[dseelam1]

I have updated the data source for some of the tables in the model in Power BI Desktop from SQL-server to BigQuery. I have updated all the column names and functions accordingly and when refreshed data is getting pulled from BigQuery. However, when I am trying to apply those canges to .pbx file before I can publish them to Web Power BI service, it fail with below error.

"ADBC: Could not create dataset _bqadbc_temp_tables"

Pop windows displays list of the tables I updated data source to BQ and same error message for all the tables.

I am using a Service account to connect to BQ with read access. 

I really appreciate if anyone can help with this.

[angel-zeitoune]

This seems like a bug. You can install the 04/25 version. With this version you can successfully connect without needing to grant additional permissions.

https://learn.microsoft.com/en-us/power-bi/fundamentals/desktop-latest-update-archive?tabs=powerbi-d...

[V-yubandi-msft]

Thanks for taking the time to share this in the community, it’s really helpful for other users.

[V-yubandi-msft]

Hello @dseelam1 ,
We haven’t received a response from you yet. Have you submitted a support ticket for this issue? If you have, please share the response so we can assist further. If not, we suggest submitting one so we can help resolve the issue promptly.

[V-yubandi-msft]

Hi @dseelam1 ,
May I know if your issue is resolved, or if you still need any additional help? Please let us know.

Thank you.

[RayNos]

I have resolved this issue by deleting the following M statement from my Power Query code. It had nothing to do with my Service Account settings. 

>>> Remove Implementation="2.0" 

[V-yubandi-msft]

Hi @dseelam1 ,

Thanks for posting your query in the Microsoft Fabric Community. The error

ADBC: Could not create dataset _bqadbc_temp_tables

comes up because Power BI’s BigQuery connector tries to create temporary datasets and tables in the background when it runs queries. If your service account only has read only permissions, it won’t be able to create those objects, and that’s why you’re hitting this issue.

To fix it

1. In the project linked to your BigQuery connection, grant the service account at least the BigQuery Data Editor role. That will let it create the temporary datasets and tables it needs.

2. Alternatively, you can create your own dataset and grant the service account full access to it, then use that for staging.

3. After updating the permissions, reconnect from Power BI Desktop and try applying the changes again.

With the right permissions in place, Power BI should be able to create the  _bqadbc_temp_tables dataset automatically and you’ll be able to publish without errors.

Regards,
CST_Team.

[p_subasinghe]

I am having the same issue.  However, I get the error when I am adding a data source.  I can select BigQuery, and navigate to my project, but when I try to open a folder in the schema, I get the same error.

The service account I am using has admin credentials, so it does not seem to be a permissions issue.

This is also a brand new issue.  I have been using the same service account and credentials succesfully for over a year.  Suddenly, I get this error.

I am thinking this is a microsoft issue that needs to be fixed.  I am able to query the data source directly with M code as a workaround.  But it is a pain to not be able to select tables from our DWH from the GUI.

[V-yubandi-msft]

Hi @p_subasinghe ,

Here are some steps that have helped others in similar situations.
If you’re using the legacy connector, try removing Implementation="2.0" from your M code. This will trigger fallback behavior, but note that it may default to US settings unless you specify otherwise.

Reconnect using the new connector and check that the region matches your dataset. If you continue to see errors in the GUI, consider submitting a ticket to Microsoft, as this could be related to a recent connector update or a change in default behavior.

You can raise a support ticket with the Microsoft team they’ll review the issue and get in touch to help resolve it.
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thank You

[V-yubandi-msft]

Hello @p_subasinghe ,
If you have received any updates or replies from Microsoft Support, please share them here. Your experience could assist other community members who are dealing with similar issues.

[dseelam1]

After providing data editor privileges to the service account and disabling the Use new Google BigQuery connector implementation option I am getting below error:

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 g4s-data-management/US/job_f778c530_543c_4270_8335_802bd5c0d5ee contained 2 error(s). First error message: Not found: Dataset Org-data-projectUK:UK_Dataset was not found in location US
Details:
DataSourceKind=GoogleBigQuery
DataSourcePath=GoogleBigQuery
AdbcStatus=1
SQLState=
NativeError=0

[V-yubandi-msft]

Hi @dseelam1  ,

Just following up were you able to fix the issue, or do you still need assistance?

@RayNos ,suggested a workaround that has helped others try removing Implementation="2.0" from your Power Query M code. This may resolve the connector problem and restore access.

Thank You.

[V-yubandi-msft]

Hi @dseelam1 ,
The service account permissions are fine now, but the error is due to a region mismatch between your BigQuery dataset location and the region Power BI is trying to run jobs in. Aligning the dataset region and the connector region should fix it.

The error message you’re seeing

Not found: Dataset Org-data-projectUK:UK_Dataset was not found in location US

means that Power BI is trying to access the dataset in the US region, but your dataset is located elsewhere (most likely in the UK/EU). BigQuery datasets are region specific, so if there’s a mismatch between the region where the query is run and where your dataset is stored, this error will occur.

Here are a few things to check

1. Verify the location of your UK_Dataset in BigQuery.
2. Ensure that Power BI’s BigQuery connector is set to the same region as your dataset. If the connector is set to US and your dataset is in EU/UK, this error will appear.
3. Try reconnecting using the new connector implementation and select the correct region for your project/dataset.
4. If changing the connection region isn’t possible, another option is to recreate the dataset in the region Power BI is using, though this may not always be ideal.

Regards,
Yugandhar.

[dseelam1]

Hi @V-yubandi-msft , Thanks for the reply.

Service account already has required edit permissions for the same dataset  where my source table reside. 
If its enough, how do I make it use the same dataset for staging?
I dont see any options within configs to provide this specific staging details.

[dseelam1]

If I try to publish changes to web service without applying the changes, I am getting below error:

"

Request ID 29eb23c0-6a24-4a6d-bba0-453c37ba5797
Activity ID 34601b08-6339-407a-806c-b0c050279eb7
Service URL https://wabi-us-central-a-primary-redirect.analysis.windows.net/
Error Code ModelUnappliedChangesSizeTooBig
Session ID 34601b08-6339-407a-806c-b0c050279eb7
Client version 2.147.909.0

"