Error on the refresh from Google BigQuery data to power bi service report

[boullen]

Hello,

I getting different error-messages when im trying to refresh one of my Power BI Service reports that i havent been seen before.

Its the first time i creating a report with an connection to a Google Big Query database so i dont have so much knowledge. 

But...

In this report i have 3 connections to 3 different databases, 2 from an local SQL server and the third from Google big query. This 3 different datasets are connected in power query with a key from every 3 sources. The refresh and connection works fine in the desktop app, but as soon as i publish it and tries to refresh i get the error message:

Error in data source: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The key didn't match any rows in the table."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Key","detail":{"type":1,"value":"[Name = "centiro-p-eu-p20"]"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}},{"code":"Microsoft.Data.Mashup.ValueError.Table","detail":{"type":1,"value":"#table({"Name", "Description", "Data", "Kind", "DisplayName"}, {})"}}],"exceptionCulprit":1}}} Table: LogistikPartnerShared. Cluster-URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net Activity ID: 35bd45bb-193f-4a84-afad-f02552c74a2d Request ID: cde450f6-736e-a100-36f0-7473771cea0c Time: 2023-12-10 20:52:27Z

I have tried to change the privacy in the desktop and tried both eauth and basic credentials.

I hope I am posting this thread in the right place and am very grateful for any replies. Thank you.

[Anonymous]

Hi @boullen ,

Please try to merge on-premises and cloud data sources.

For more details, you could read related document: Merge or append on-premises and cloud data sources - Power BI | Microsoft Learn

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[boullen · Accepted solution]

Maybe I wasn't clear when I described the problem... Here's a clearer explanation. I used Power BI's built-in connector "Google BigQuery" on both the Desktop and Service versions but consistently encountered the above error message. Instead of using the built-in version, I opted for an ODBC/DSN connection with the Simba ODBC driver for Google BigQuery. With this, and after adjusting privacy settings to ensure consistency between the service and the desktop, I eventually managed to get the refresh to work on the service.

[aj1973]

Hi @boullen 

Not sure why you think it is BigQuery the problem but I think you need to set up a Gateway connection.

Here is how to connect to https://learn.microsoft.com/en-us/power-query/connectors/google-bigquery