Hi Again! I’m back with another post on BigQuery, Power BI and Google Sheets. Today, we’ll be looking at how to connect Power BI and Google Sheets.

Ideally, I’d want to create a _DirectQuery_ connection between Power BI and Google Sheets, because the data in my Google spreadsheet changes frequently, and we need the latest data to be reflected in the [Power BI report](/content/power-bi-consulting-service-and-solutions/index.html). Unfortunately, Power BI doesn’t currently support a _DirectQuery_ connection to Google Sheets.

This is where BigQuery will help us out. We’ll try to use BigQuery as an intermediary to connect Power BI to Google Sheets and access the data we need in the way we need it. We’ll need to explore creating a _DirectQuery_ connection between Power BI and an external table on BigQuery.

#### What is a Direct Query Connection?

[Power BI](/content/services/visual-analytics/power-bi-services/index.html) offers different ways to connect to its data sources – Import, _DirectQuery_ and Live connection. A _DirectQuery_ Connection is when the data isn’t stored in the [Power BI](/content/power-bi-consulting-service-and-solutions/index.html) Server but in the data source itself. Every time you refresh the report, it queries the data from the source and gets it.

Power BI recommends that it’s best to use the ‘Import’ connection whenever possible. However, if we have data changes frequently and reports that must reflect the latest data, then _DirectQuery_ fits best. Performance might take a small hit, but it becomes essential when dealing with huge datasets which can’t be imported to Power BI.

Data sources which allow for direct connections are Google BigQuery, SQL Server, [Azure Data Lake](/content/blogs/cloud-services-how-to-choose-between-azure-and-aws/index.html) etc., You can find more information about Power BI supported [data sources here](https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources).

#### What is an External table?

Google BigQuery has two ways in which it stores its data:

1. Native Table – this is where the data gets imported and is loaded into [BigQuery](/content/blogs/importing-unnested-data-from-bigquery-to-powerbi/index.html) or a table is created in BigQuery and data is inserted. This consumes storage on BQ
2. External Table – this is where the data doesn’t get imported but stays in the source. This table is linked to the data source and it gets the data every time the associated BigQuery table is queried. This doesn’t consume storage on BQ

Any data which is stored on Google Drive can only be accessed as an external table on BigQuery and this goes for Google sheets as well.

#### Time for some Action!

I have a Google spreadsheet named ‘ _Test\_data’_ on my Google Drive. I’ll load this spreadsheet to BigQuery using the web console.

I’ll presume that you have a BigQuery account, a project and a dataset to use for this exercise. Pick a dataset and click on the ‘ _Create Table’_ option. You’ll then be presented with a screen like below. Fill in the details and your BQ external table is ready.

Now it’s time to test the _DirectQuery_ connection in Power BI. So, open Power BI and click on the ‘ _Get Data’_ option. Select the ‘ _Google Big Query’_ and click ‘ _Connect_’. In the resulting window, navigate to the dataset containing the ‘ _Test\_data’_ table.

Well, that’s strange! I had 4 tables in my dataset but I can only view 3 of them. This is because the Power BI [Google BigQuery](/content/blogs/importing-unnested-data-from-bigquery-to-powerbi/index.html) Connector doesn’t support _External_ BigQuery tables. It only supports the _Native_ BigQuery tables. Since _Test\_data_ is an external table, it doesn’t get listed here.

This information is not mentioned in the [Power BI documentation for Google BigQuery](https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-bigquery) either. So, we’ll have to look at alternative ways to get that data.

#### Is there a workaround?

Yes. We’ll have to convert our _External_ table into a _Native_ table on BigQuery so that Power BI can access it. Let’s look at how to do get this done.

##### Creating a new BigQuery table

This can be done on the BQ web console with a DDL statement. Here it goes

|     |
| --- |
| CREATE TABLE \`project\_name.dataset\_name.Test\_data\_NEW\`<br>AS SELECT \* FROM \`project\_NAME.DATASET\_NAME.TEST\_DATA\` |

The above statement creates a new table named ‘ _Test\_data\_new_’ and copies the data from ‘ _Test\_data’_ table onto it. Now we have a _Native_ table in BigQuery containing the google spreadsheet data.

It’s also possible to write a [scheduled query](https://cloud.google.com/bigquery/docs/scheduling-queries#console) and update the _Native_ table on a recurring basis once it has been created. Note that the minimum time that BigQuery allows you to refresh a table is at 15 minutes. So, with this solution, your report gets updated data every 15 minutes. You could use the below query to get started.

|     |
| --- |
| INSERT \`project\_name.dataset\_name.Test\_data\_NEW\`(Name, AGE, LOCATION)<br>SELECT \* FROM \`project\_NAME.DATASET\_NAME.TEST\_DATA\` |

##### Connecting to Power BI

Let’s retrace our steps. Open Power BI and click on the ‘Get Data’ option. Search for _Google BigQuery_ and select it. Once it opens up, navigate to your new table ( _Test\_data\_new_) and click on the _Load_ button.

You’ll then be presented with a _Connecting settings_ window, where you need to select the **_DirectQuery_** option and click OK.

There you go! I can finally access my Google spreadsheets data in Power BI and the report gets updated data every 15 minutes. Now, that is a perfect solution to this problem. Please feel free to ask any questions by contacting me through the contact box!