[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.