Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PiotrStawicki
Frequent Visitor

How to connect PowerBI with google docs via BigQuery

Hi all,

 

I have a problem with connecting Power BI dataflows (tried also dataset) to the BigQuery view based on google docs, shared in my organisation.

The problem is not with connection to BigQuery in general, because I've already created several dataflows based on the BigQuery views and they work and refresh correctly. 

But when trying to connect to this view based on G doc, I got this error:

PiotrStawicki_1-1623236567522.png

I found out that some different permissions are necessary, but I don't know how to configure it in Power BI (I can query this view without any restrictions in the BigQuery SQL Console), probably my organisational account permissions are OK but for some reasons, looks that Power BI doesn't check my drive.readonly scope during authentication.

 

BigQuery is used as "source of truth" in the company and I'm pretty sure that it's possible to integrate PowerBI with such common solution, I'll be grateful for suggestions on what I can do to properly configure the connection to dataflow 🙂 

 

1 ACCEPTED SOLUTION
PiotrStawicki
Frequent Visitor

Hi @v-janeyg-msft and thanks for your answer.

 

The dataflow which I'd like to configure bases only on this single view. But unfortunately Datasource Credentials do not apply there, as the error pops up earlier, when I try to configure the query for the first time. With this error, it's impossible to even save & close the dataflow edit mode, so also configuring the credentials in settings.

 

Anyway, I found one solution in some other thread, which maybe is not the most efficient but so far it works in my case 🙂  

 

Thread: https://community.powerbi.com/t5/Power-Query/How-to-connect-BigQuery-Sheets-table-using-the-Power-BI...

" Schedule a query in BQ (e.g. SELECT * FROM [name of your google sheet table in bigquery]) and run that daily to ensure you capture new changes in the Google sheet. This allows you to store the results of your scheduled query in a separate table which you will then use in PowerBI. This way PowerBI doesn't have to authenticate with Google Sheets.""

View solution in original post

5 REPLIES 5
v-janeyg-msft
Community Support
Community Support

Hi, @PiotrStawicki 

 

Any uodates?

PiotrStawicki
Frequent Visitor

Hi @v-janeyg-msft and thanks for your answer.

 

The dataflow which I'd like to configure bases only on this single view. But unfortunately Datasource Credentials do not apply there, as the error pops up earlier, when I try to configure the query for the first time. With this error, it's impossible to even save & close the dataflow edit mode, so also configuring the credentials in settings.

 

Anyway, I found one solution in some other thread, which maybe is not the most efficient but so far it works in my case 🙂  

 

Thread: https://community.powerbi.com/t5/Power-Query/How-to-connect-BigQuery-Sheets-table-using-the-Power-BI...

" Schedule a query in BQ (e.g. SELECT * FROM [name of your google sheet table in bigquery]) and run that daily to ensure you capture new changes in the Google sheet. This allows you to store the results of your scheduled query in a separate table which you will then use in PowerBI. This way PowerBI doesn't have to authenticate with Google Sheets.""

Hi, @PiotrStawicki 

 

I would like to ask, when you connect to the data source, whether there is a query executed on the BQ, which can cause problems.

 

Best Regards

Janey Guo

Hi,

 

I'm sorry for late response. I'm not sure how to check or answer to this question, but I could easily connect correctly to this BigQuery view by other environments, for example by BigQuery consone and read the data using some standard queries, so I suppose the problem is not on BigQuery side.

The problem occured only trying to connect to this view by Power BI query editor
But as I mentioned in my last comment, the solution that I found in some other thread helped and it works this way. So I think you can close this thread because problem is solved, thanks for your support 🙂

Regards,

Piotr

v-janeyg-msft
Community Support
Community Support

Hi, @PiotrStawicki 

 

Are there other data sources in your report? You can view and configure the data source credentials in the corresponding dataflow settings.

v-janeyg-msft_0-1623397158833.png

If it doesn't solve your problem, please fell free to ask me.

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors