Use Google Cloud BigQuery as Data Source in Power BI

BigQuery is Google’s serverless data warehouse in Google Cloud. Power BI can consume data from various sources including RDBMS, NoSQL, Could, Services, etc. It is also easy to get data from BigQuery in Power BI.

In this article, I am going to demonstrate how to connect to BigQuery to create visuals.

Prerequisites

Google Cloud account is required. You can register a trial account.

In BigQuery, there is a public dataset named world_bank_intl_debt in project bigquery-public-data. We are going to use table international_debt to create some visual.

The details about this table is available here: https://bigquery.cloud.google.com/table/bigquery-public-data:world_bank_intl_debt.international_debt?pli=1&tab=details

Cost of querying public data sets

Public data sets are paid by Google for storage but you need to pay for querying it.

Connect to BigQuery in Power BI

Open Power BI and create a new file.

In the Home tab and click Get Data button.

In the Database tab of the opened window, select “Google BigQuery”.

image

Click Connect button to continue.

Click Sign in button to sign into your Google Could account.

image

In the opened window, click Allow button to allow Power BI Desktop to view and manage your data in Google BigQuery:

image

Click connect button once signed in to continue.

Select the Required Data Tables

The hierarchy of BigQuery is: Project -> DataSet -> Table.

In the opened window Navigator, expand bigquery-public-data project.

image

For this tutorial, we just need international_debt table under world_bank_intl_debt dataset.

image

Click Load button to load the data.

And then you can setup Connection settings. In this case, let’s choose Import which will bring a copy of the data into Power BI.

Please note you will pay for querying the data. There are 1,359,644 records in this table. You can customize the query to only retrieve sample data to reduce the cost.

image

Once imported, the following fields are available to use:

image

Create a visual using the data imported

With the data available, we can now easily create a line chart by using field year as Axis and field value as Values.

image

You can create as many visuals as you can do with any other data sources.

image

Summary

It is very easy to consume Google BigQuery data in Power BI. You can create joins when drafting the queries or implement within Power BI.

For performance and cost consideration, you may choose to physicalise some data in BigQuery and then query the aggregated data into Power BI.


info Last modified by Raymond at 5 months ago copyright This page is subject to Site terms.

More from Kontext

Pandas DataFrame Plot - Scatter and Hexbin Chart

local_offer plot local_offer pandas local_offer jupyter-notebook local_offer python

visibility 78
thumb_up 0
access_time 5 months ago

 In this article I'm going to show you some examples about plotting scatter and hexbin chart with Pandas DataFrame. I'm using Jupyter Notebook as IDE/code execution environment.  Hexbin chart &nbs...

open_in_new Code snippets

Pandas DataFrame Plot - Area Chart

local_offer plot local_offer jupyter-notebook local_offer python local_offer pandas

visibility 86
thumb_up 0
access_time 5 months ago

This article provides examples about plotting area chart using  pandas.DataFrame.plot  or  pandas.core.groupby.DataFrameGroupBy.plot   function. ...

open_in_new Code snippets

Pandas DataFrame Plot - Pie Chart

local_offer plot local_offer pandas local_offer jupyter-notebook local_offer python

visibility 1755
thumb_up 0
access_time 5 months ago

This article provides examples about plotting pie chart using  pandas.DataFrame.plot  function. Prerequisites The data I'm going to use is the same as the other article  ...

open_in_new Code snippets

Pandas DataFrame Plot - Line Chart

local_offer plot local_offer pandas local_offer jupyter-notebook local_offer python

visibility 153
thumb_up 0
access_time 5 months ago

This article provides examples about plotting line chart using pandas.DataFrame.plot function. Prerequisites The data I'm going to use is the same as the other article  ...

open_in_new Code snippets

comment Comments (8)

comment Add comment

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

R
Raymondarrow_drop_down
No worries. Once last thing you probably can try is to clear browser Cookies.
format_quote

person padma access_time 5 months ago
Re: Use Google Cloud BigQuery as Data Source in Power BI

Yes, it's not working for me even though i deleted from third part apps. At least we can restrict the access. Thank you so much for your help..  

reply Reply
P
padmaarrow_drop_down

Yes, it's not working for me even though i deleted from third part apps. At least we can restrict the access. Thank you so much for your help..  

reply Reply
R
Raymondarrow_drop_down

Hmm my understanding is that once you click Remove Access button that account will be gone when you next time try to connect to BigQuery from Power BI Desktop. At least that works for me. That one will only shows up there because you have not removed it from 'Apps with access to your account page': https://myaccount.google.com/permissions.

Better to raise this to Google support if it doesn't resolve the problem.

format_quote

person padma access_time 5 months ago
Re: Use Google Cloud BigQuery as Data Source in Power BI

Hi Raymond,

Thank you so much for your prompt reply, it helped me to remove access. We can change the password too..

But I am trying to remove account from that third party app. Unfortunately, there is no option to "Remove account" option. It still showing my account in that like this. It will be a great help if we find a way to remove account..


Thank you

Padma

reply Reply
P
padmaarrow_drop_down

Hi Raymond,

Thank you so much for your prompt reply, it helped me to remove access. We can change the password too..

But I am trying to remove account from that third party app. Unfortunately, there is no option to "Remove account" option. It still showing my account in that like this. It will be a great help if we find a way to remove account..


Thank you

Padma

format_quote

person Raymond access_time 5 months ago
Re: Use Google Cloud BigQuery as Data Source in Power BI

Hi Padma,

This is a good question. 

Once you authorize Power BI to access Big Query data, you can follow the steps below to remove it: 

  • Go to your Google Account.
  • On the left navigation panel, select Security.
  • On the Signing in to other sites panel, select Signing in with Google (https://myaccount.google.com/permissions). 
  • Select the site or app you want to remove. For this case, it's Power BI Desktop
  • Select Remove Access.

If you are trying to remove account access to BigQuery, you can use IAM feature in Google Cloud Console.


I hope that answers your question. Let me know if you have other questions.

reply Reply
R
Raymondarrow_drop_down

Hi Padma,

This is a good question. 

Once you authorize Power BI to access Big Query data, you can follow the steps below to remove it: 

  • Go to your Google Account.
  • On the left navigation panel, select Security.
  • On the Signing in to other sites panel, select Signing in with Google (https://myaccount.google.com/permissions). 
  • Select the site or app you want to remove. For this case, it's Power BI Desktop
  • Select Remove Access.

If you are trying to remove account access to BigQuery, you can use IAM feature in Google Cloud Console.


I hope that answers your question. Let me know if you have other questions.

format_quote

person padma access_time 5 months ago
Re: Use Google Cloud BigQuery as Data Source in Power BI

Hi,

I tried to login google big query and successfully connected. But I want to remove my sign in info and account from google big query. Please suggest how to remove that.

reply Reply
P
padmaarrow_drop_down

Hi,

I tried to login google big query and successfully connected. But I want to remove my sign in info and account from google big query. Please suggest how to remove that.

reply Reply
R
Raymondarrow_drop_down

Hello,

I'm not sure whether I understand your questions correctly or not. Once your import the data using Import or Direct Query, you can then customise through Power Query Editor in Power BI:

https://docs.microsoft.com/en-us/power-query/power-query-quickstart-using-power-bi

format_quote

person Joana Barbosa access_time 2 years ago
Re: Use Google Cloud BigQuery as Data Source in Power BI

Hi,

Thank you for your post.

Do you know how to customize de query in power query?

Couldn't find it in any documentation.


Thanks.

Joana Barbosa

reply Reply
account_circle Joana Barbosa

Hi,

Thank you for your post.

Do you know how to customize de query in power query?

Couldn't find it in any documentation.


Thanks.

Joana Barbosa

reply Reply

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.


Learn more arrow_forward