Power Query: Google it

Welcome to our Power Query blog. This week I look at the Google Sheets connector, available in the Power BI Desktop platform.

I’ve moved over to Power BI for this week’s blog, since there are far more connectors available there when extracting data. To get to the ‘Google Sheets’ connector, I start with the ‘More…’ option on the ‘Get Data’ dropdown:

There are a lot of connectors, which are not in alphabetical order!

Rather than scrolling through the ‘All’ tab, it is quicker to go to the ‘Other’ tab:

Roughly half-way down, I find ‘Google Sheets (Beta)’. What is Beta? If I click on this option, the answer is revealed:

This is a work in progress, so let’s see what I can do. I click Continue:

I am prompted for the URL of my Google Sheet. Here’s one I made earlier:

I enter the URL:

When I click OK, I am prompted to ‘Sign in’.

I am then asked to trust Power BI (I’ve removed the account name from the visual).

I click Continue, and receive confirmation:

The GoogleSheets dialog now shows I am signed in.

I can now Connect:

On the Navigator dialog, I choose Sheet1:

The data looks good, but as always, I choose ‘Transform Data’ so I can check it out.

There are only two [2] steps. I check out the ‘Source’ step:

This gives me the M code for accessing Google Sheets:

Most of this, of course is the URL.  The main M function is:

Moving back to the ‘Navigation step’, I can promote the top row to headings using ‘Use First Row as Headers’ on the Transform tab :

I choose this option:

The Power Query engine also applies a ‘Changed Type’ step, and my data is ready to be applied to the Power BI model.

Come back next time for more ways to use Power Query!