[Business Intelligence] Connecting a Google Sheet to Power BI as a Data Source

Level of Difficulty: Beginner – Senior.

The market for data collection, visualisation and analysis tools spans across many different providers on many different platforms and in some cases, the best way to become exposed to the variety of tools is by mixing and matching between them. So why not visualise a Google Sheet on Power BI? I learnt quite a bit about connecting Power BI to different sources and I experienced some of the power that Google Sheets offer through this solution. Want to try it out for yourself? Here’s how.

What are the steps?

The steps to using a hamburger menu to hide and show a filter pane in Power BI are:

  1. Create the Google Sheet Dataset in Google Drive
  2. Change the Permission
  3. Publish the Google Sheet to the Web
  4. Connect the Dataset to Power BI
  5. Select the Applicable Dataset

Let’s deep dive into the above steps.

Create Google Sheet Dataset in Google Drive

Go to Google Drive and create a new Google Sheet or upload an existing Google Sheet:

Download the Exoplanet Dataset available on data.world if you would like to follow along with the post. Please feel free to use your own dataset if you have one available.

The dataset is a CSV that needs to be turned into a Google Spreadsheet. Ensure that you are using a Google Spreadsheet and not an Excel or CSV document. For the sake of this post, I’ll only be using the first 2 000 records of the dataset. I would strongly suggest using less records with less columns too. Using all of the data will be extremely slow when connecting to Power BI.

Change the Permissions

In order to access the report from Power BI, the dataset needs to be accessible by anyone with the link, especially when publishing the dataset to the web. Change the permissions by clicking on Share and making it publicly available to anyone with the link:

Publish to the Web

Navigate to file then select Publish to the web:

Make sure that you link the publish to the correct tab (if you do not want to publish the whole document). Hit ‘Publish and ‘OK’ when prompted:

Copy the link:

Connect the Dataset to Power BI

Open Power BI, click on Get Data and select Web as the source:

Enter the copied publish URL (found after publishing) and enter it into the URL section, with basic authentication selected:

Select the Applicable Dataset

Once Power BI has connected to the dataset, you’ll be prompted to select the applicable tables (or tabs/datasets) that you can transform and load. Select the applicable tables then select Transform Data:

To get the correct row promoted as headers, you’ll need to remove the first row then use first row as headers. Please note that this may vary across use cases and datasets.

When you’re happy with the structure of your data, hit Close & Apply:

Did this work for you? Feel free to drop a comment below with any issues you may have faced, or even any suggestions you might have.

Published by Jacqui Muller

I am an automation architect by current profession who enjoys dabbling in software development, RPA, IOT, advanced analytics, data engineering and business intelligence. I am aspiring to complete a Master's degree in Computer Science within the next two years. My competencies include a high level of computer literacy as well as programming in various languages. I am passionate about my field of study and occupation as I believe it has the ability and potential to impact lives - both drastically and positively. I come packaged with an ambition to succeed and make the world a better place.

One thought on “[Business Intelligence] Connecting a Google Sheet to Power BI as a Data Source

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: