Unable to load my bigquery data into PowerBI (already tried the solutions posted in previous posts)

[Avrahad]

Hi I'm getting the following error when trying to load my biguqey data into power bi for the first time, it seems to have something to do with the fact that the table uses data from a google sheet. At first it wouldn't even allow me to see the table it only showed me the dataset names, so I created a view and now I can see the View under the dataset but I can't actually see/load the data and I get the error shown below. The account I'm using has owner permissions, and I've tried typing in the billing account id and protect id in the advanced settings but that didn't do anything. I have no idea how to resolve this:

DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (100) Error interacting with REST API: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.
Details:
DataSourceKind=GoogleBigQuery
DataSourcePath=GoogleBigQuery
OdbcErrors=[Table]

[Anonymous]

Thanks for the reply from Shravan133.

Hi @Avrahad , 

Have you solved your problem? If so, can you share your solution here or mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Avrahad]

I switched to an official organizational account with my client and set up billing and redid all the steps and it did not work.

[Avrahad]

Hi, I have already done most of those things and it did not resolve the issue, but I will see if setting up an official organization account for the billing account will make a difference. Once I have that set up with my client I will confirm if Shravan133's solution worked.

[Shravan133]

The error you're encountering indicates that there's an issue with the permissions related to accessing Google Sheets data via BigQuery when loading it into Power BI. Here are the steps to troubleshoot and resolve the issue:

Step 1: Check Google Drive Permissions

Ensure that the service account used by BigQuery has the necessary permissions to access the Google Sheets file. This typically involves granting the "BigQuery Data Editor" role and ensuring that the service account has access to the specific Google Sheets file.

1. Share the Google Sheet:
   • Open the Google Sheets file.
   • Click on "Share" and add the service account email as an editor.
2. Grant Permissions in BigQuery:
   • Go to the Google Cloud Console.
   • Navigate to IAM & Admin -> IAM.
   • Ensure that the service account used by BigQuery has the "BigQuery Data Editor" and "BigQuery Job User" roles.

Step 2: Enable BigQuery API

Ensure that the BigQuery API is enabled in your Google Cloud project.

1. Enable BigQuery API:
   • Go to the Google Cloud Console.
   • Navigate to APIs & Services -> Library.
   • Search for "BigQuery API" and ensure it is enabled.

Step 3: Setup Billing Information

Ensure that your Google Cloud project has a billing account attached, as accessing Google Sheets via BigQuery may incur charges.

1. Set Up Billing:
   • Go to the Google Cloud Console.
   • Navigate to Billing.
   • Ensure that your project is linked to a billing account.

Step 4: Configure Power BI Connection

Ensure that Power BI is configured correctly to access BigQuery.

1. Use a Service Account Key:

   • In the Google Cloud Console, go to IAM & Admin -> Service Accounts.
   • Select your service account and create a key (JSON format).
   • In Power BI, use this JSON key file to authenticate the connection to BigQuery.

2. Add Advanced Settings (if required):

   • When connecting to BigQuery in Power BI, go to the "Advanced" settings.
   • Add your billing project ID in the "Billing Project" field.

Step 5: Create a View with Proper Permissions

Since you mentioned creating a view, ensure that the view has the correct permissions.

1. Create View in BigQuery:

   • Ensure that the view references the Google Sheets data correctly and that the service account has access to the underlying Google Sheets.

2. Check View Permissions:

   • In the BigQuery console, ensure that the view has proper access permissions.
   • Ensure that the service account can run queries on the view.

Example Steps to Share Google Sheets:

1. Open your Google Sheets file.
2. Click on "Share" in the top right corner.
3. Add your BigQuery service account email (e.g., service-account-name@project-id.iam.gserviceaccount.com) with "Editor" permissions.

Example Steps to Create Service Account Key:

1. Go to the Google Cloud Console.
2. Navigate to IAM & Admin -> Service Accounts.
3. Select the service account you are using.
4. Click on "Keys" -> "Add Key" -> "Create New Key".
5. Choose JSON format and download the key file.
6. Use this key file to authenticate in Power BI.

By following these steps, you should be able to resolve the permission issues and successfully load your BigQuery data, including Google Sheets data, into Power BI. If the problem persists, you might want to review the specific error messages and logs in Google Cloud to get more details on what might be causing the permission denial.

[Avrahad]

I followed all of these steps unfortunately it didn't change anything, seems like power bi just can't handle bigquery data derived from google sheets, but as long as you import into bigquery through a csv format it works.

[tattunglam]

Hi @Avrahad , I have the same problem. Please share your solution if you have done it.

[ReportRanger]

Hi @Avrahad I see you haven't been able to find a solution yet. Maybe you can try connecting BigQuery through a 3rd party instead of using the direct BigQuery connector, it could work hopefully. I use windsor.ai to connect my data from multiple sources and they have a BigQuery connector which you can use to connect to Power BI. Hope it helps !