BigQuery connection for Power BI through ODBC drivers

1
votes

I am facing an issue while trying to connect BigQuery to Power BI using Simba ODBC driver. Does anybody faced an issue like this and can help me with it? Any help or suggestion is appreciated! Thank you very much in advance!

On BigQuery, I have a table which is connected directly from Google Sheets using its URL. First I tried to connect BigQuery or Google Sheets to Power BI but it gave me authentication errors. Then I learned that I can use ODBC drivers to prevent this issue. I downloaded and configured the driver and tested, it worked successfully. Then I created the connection through ODBC on Power BI, right now I can see and connect all the table I have in my project but the table I need is not there still. Could this issue be because of the Google Sheets access settings, because it is not publicly accessible?

3
google-sheetsgoogle-bigqueryodbcdatabase-connectionpowerbi
Power BI has a direct connector to BigQuery(docs.microsoft.com/en-us/power-bi/desktop-connect-bigquery) – James
@James thanks for the answer, but it doesn't work because the table in BigQuery which I want to connect is a Google Sheets sourced table, so Power BI doesn't accept that I guess. – Yagmur

3 Answers

0
votes

As James mentioned, Power Bl has a direct connector to BigQuery Connect to BigQuery in Power BI Desktop. You should give it a try.

I read that Google BigQuery can query Google Sheets just like any other table. Look here: BigQuery and Google Sheets. The MS documentation does not mention any limitation or restriction related to Google Sheets so I think there won't be any other issue.

Also, did you follow any tutorial or doc? Can you share it?

0
votes

Some "Google resources” (projects, files, etc) tend to be hidden so external tools can't see or access to them, even when there is a connection between tools.

This user had a similar situation BigQuery Sheets and Power BI connector, take a look at it. They suggested to enable the Google Drive API so they can manipulate file's data.

Another similar situation was seen here Connecting Google Sheets to Power BI, where they connected the Spreadsheet directly to Power BI. This is more related, as you prior mentioned, to the file's privacy settings.

0
votes

I have created connection power bi to big query engine using ODBC and Simba driver, here

I'm explaining the procedure

1) Download Simba driver for BigQuery from Download and install

enter image description here

2) Search ODBC Driver (64bit) in the windows search bar

enter image description here

3) From that you click and move to system DNS, there you can find the Google Big Query Driver installed

enter image description here

4) Select Driver and Click configuration option then select OAuth Mechanism is User Authentication and click the sign-in button

enter image description here

5) It redirects to login and permission allow the window

enter image description here

6) Once you allowed then you will get a refresh token. Copy that one and past on ODBC driver refresh token text box and select your project to query and get the data

enter image description here

7) Go to Power bi and select get data option select ODBC driver, click connect option

enter image description here

8) Data source name as google big query and next text box leave blank and last text box write your query

enter image description here

9) finally you will get the result as given below.

enter image description here