Select Distinct Logo Clear Background

Business Analytics Blog

Connect Power BI to Google Big Query

Connect Power BI to Google Big Query

A Quick Guide to Connecting Power BI to BigQuery

Power BI is a leading business intelligence platform, with powerful features for self service analysis

BigQuery is a fully managed cloud based data warehouse platform for you to manage your data

The great thing about both of these platforms is that they both have a really useful free tier, enabling you to use some of the excellent public datasets to build your skills

This blog focuses on the use of power BI with one of those really useful datasets and assumes that you already have access to the google cloud platform, BigQuery and the Public datasets

Google Public Datasets are a great resource for learning

Google has made a variety of public datasets available, You can access these and query them and use them to develop your skills

The marketplace in Google Cloud Platform is where you need to head to find the public datasets

From the top menu on the Google Cloud Platform, hit the ‘Hamburger’ icon and click on the Marketplace option

Google Cloud Marketplace menu l;ayout

From here you need to select the Analytics category, and then under type, select Data sets

Google Cloud Platform marketplace, with public data sets

We will be working with the ‘Iowa Liquor Retail Sales’ data set

Overview of the Iowa Liquor Retail Sales public data set

Retailers in Iowa buy liquor from the State of Iowa, which controls the wholesale distribution of liquor for retail sale. This dataset has all the wholesale liquor purchases by retailers in Iowa since January 1, 2012. The dataset shows every wholesale order of liquor by any store that sells liquor, such as grocery stores, liquor stores, convenience stores, etc. The dataset has information about the store and its location, the brand and size of the liquor, and how many bottles were ordered.

This dataset is useful for analysing liquor sales in Iowa, as it covers all the retail liquor sales in the state. It is also a large and clean public dataset of retail sales data that can be used for other purposes, such as predicting when a store will run out of stock, forecasting the demand for liquor, and solving other retail supply chain problems.

The great thing about this data set is that is real data, updated monthly and it has plenty of variation for you to try different techniques on

To find out more about the data follow this link
Iowa Liquor Sales |

Click the view data set button

Iowa Liquor Retail Sales link
You are then taken to your Google BigQuery platform, straight to the data set

You can also now see lots of other public data sets

Google Public Data Sets Iowa liquor sales in Google Public Data Sets

Effectively this is a database with a single table called ‘Sales’

Expand the chevron and you will see the table

Iowa liquor sales schema

Within BigQuery you can carry out an exploration of the data and become familiar with the data types it contains

How to Connect Power BI to Google Big Query

We will connect our Power BI Desktop to this and start visualising the data

Step 1. Click on Get data on the ribbon, then click more… at the bottom of the menu

Get data menu in Power BI

Step 2. Select Database, then scroll down to Google BigQuery

Connect Power BI to Google BigQuery menu

Step 3. Connect Power BI to Google BigQuery by clicking the connect button

If you are already logged in then thats all there is to do

If you are not already logged in then you may be taken to a login screen for authentication

Connect to Google BigQuery

From here you can go into the advanced option and explicitly write the SQL to retrieve the data you need

Or, if you click OK you are presented with a navigator screen

Scroll down to find the Iowa Liquor sales database, then select the sales table. Ensure there is a green tick next to the table

If the data set was small then you could simply click load….BUT This data set contains over 27 million rows so we will need to strip it back

We will just load the last 12 months as an example

Click Transform Data then Import

Connect Power BI to Google Big Query

On the date column, select the drop down arrow

Then date filters, In the previous

Now make sure the filter is set as below to retain the newest 2 years of data

Filter rows in power query for the last 2 years

(You may want to filter down further to shrink the data set and avoid costs, so feel free to add more filters before loading the data to Power BI)

For this example, we want a large data set so we will use the full 2 years

Now select Close and Apply from the ribbon

You will then see a loading screen with a count of how many rows have been loaded

Typically there is around 2.5m rows per year

Power BI loading data

After the data has loaded you can then begin to visualise the data

We will look at sales over the two years

Select a line chart visual and set the X axis to date (month)

set the Y axis to sale-dollars

finally set the year as the legend to compare the last two years on the same chart

Iowa Liquor sales in dollars 2021 v 2022

Connect Power BI to Google BigQuery : Conclusion

As you have seen its actually quite easy to connect Power BI to BigQuery and use these public data sets for your own learning and development

By exploring the data you can begin to see real patterns in seasonality, identify best sellers, leading stores and use some of the more complex geo location data to present the data in easy to understand ways

Just be careful not to bring too much data back or too often or you could go over the free tier for BigQuery

Subscribe to our channel to see more tips and timesavers

Select Distinct YouTube Channel

Or find other useful SQL, Power BI or other business analytics timesavers in our Blog

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work.  They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.

Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert