Power BI and Google Maps API (Address Lookup)

In this post I explain how to use Google Maps APIs to retrieve useful information out of Google Maps. The use case scenario could be getting address, postal code, etc. from existing latitude and longitude values. The data could be generated by any sort of GPS tracking device like your Garmin cycling GPS computer, your Fitbit watch etc. I know you can load your GPS tracking data into athletic social networks to analyse your activities. But, if you want to do some more specific data analytics like in which area of the city you created more power during your cycling activities then those websites might not give you what you want for free.

For instance, you can export your device data to CSV then import and append all CSV files into a Power BI model and create amazing analytical reports. How to import your CSV files into a Power BI model is out of scope of this article so I leave it to you for any further investigations.

GPS tracking devices are creating lots of data including geographic coordinates which can be easily used in Power BI. You can simply put latitude and longitude on a Map visualisation and you’re good to go.

Power BI Map using Coordinates

You can also concatenate the latitude and longitude data and use it as Location in your Map visualisation.

Power BI Map using Location

This can be done from Query Editor in M language.

Creating Location from Latitude and Longitude in Power BI

But, in some cases you need some more geo-information like Country, City, Post Code and Street Address in a table as well. Or you might want to use postal code in a slicer. In this article I show you how to get all of these information out of Google Maps by passing existing coordinates to Google Maps geocoding API.

Requirements

To be able to follow this post steps you need to:

  • Have a Google account
  • Activate the Google Maps Geocoding API
  • Find a desired API in Google Developers Guide
  • Use the API in Power BI
  • A GPS device data exported to CSV file

Activate the Google Maps Geocoding API

I assume you already have an existing Google account or you know how to create a new one, so I jump straight into the next step which is activating geocoding API. To be able to start using the Google Maps Geocoding API you need to get an API key first.

Google Maps Geocoding API

  • Scroll down and click “Get A Key”

Get Google Maps API Key

  • This will navigate you to Google Developers Console
  • Click “Yes” then click “Agree and continue”

Get Google Maps API Key 2

  • Type in a name then click “Create”
  • Get Google Maps API Key 3

You successfully created an API key. We will use this key in the next sections of this article.

Using Google Maps Geocoding APIs In Power BI

Now that we have an API key we can use lots of Google Geocoding APIs available here. As you can see there are lots of useful APIs you can use to leverage your Power BI model. In my sample I need to retrieve Country, Post Code and Street Address out of existing coordinates generated by my bike GPS computer. So I use Reverse Geocoding (Address Lookup). Here is a sample:

https://maps.googleapis.com/maps/api/geocode/json?latlng=-36.8484213,174.7457047&key=YOUR_API_KEY

If you copy and paste the above API in your web browser you can see its output which is a JSON providing detailed information about the location.

Note: Do not forget to copy and paste your API key at the end of the API.

JSON Output of Google Reverse Geocoding API

The idea is to define a function in Power BI then pass latitude and longitude to the function to get Country, Postal Code and Street Address.

How it works

  • Open Power BI Desktop
  • Get Data from Web
  • Paste the API in the URL section then click OK

Power BI Desktop Get Data From Web

  • Stick with the default and click “Connect”

Power BI Desktop Access Web Content

  • This opens “Query Editor”
  • Click “List”

Power BI Desktop Query Editor Expand List

  • Convert the list to table by clicking “To Table” button from “Transform” tab from the ribbon

Power BI Desktop Convert List to Table

  • Click on expand complex column icon (image) to expand the table then tick “formatted_address” and “types” columns then click OK

Power BI Desktop Expand Complex Column

Power BI Desktop Expand Complex Column 2

  • Expand the “types” column as well

Power BI Desktop Query Editor

  • Filter “types” column to show “Country”, “Postal Code” and “Steer Address”

Power BI Desktop Query Editor Filter Column

Power BI Desktop Query Editor 2

  • We need to the columns as rows so we need to transpose the table. So go to “transform” tab and click “Transpose”

Power BI Desktop Transpose Table

  • As you see the second row contains the column names. Click “Reverse Rows” to show the second row first

Power BI Desktop Reverse Rows

  • Click “Use First Row As Headers”

Power BI Desktop Use First Row As Header

  • Rename the query then save the model

Power BI Desktop Rename Query

Create a Function from an Existing Query

So far we loaded the geo-location information for just one coordinate. Now we need to turn the query to a function so that we can invoke it for all coordinates we have in our dataset.

A function construction is as below:

 

  • Click “Advanced Editor”

Power BI Desktop Advanced Editor

  • Change the code as per screenshot below

Power BI Desktop Create Function

1– Function internal name

2 & 4 – Input parameters

3 & 5 – Parameters’ data types

6 – Function body

7– Output

Note: The name that will be used to revoke the function is the query name which is “fn_GeoLocation” in our sample.

  • Continue modifying the codes as below then click “Done”

Power BI Desktop Function Parameters

  • What we have done above is that we replaced the static latitude and longitude with parameters. We used “&” to concatenate both sides of the source.
  • As soon as you click “Done” Power BI detects the query as a function

Power BI Desktop Invoke Function

  • To test the function just click “Invoke” and enter latitude and longitude then click OK

Power BI Desktop Invoke Function 2

Power BI Desktop Invoke Function 3

  • Delete the invoked function step

We successfully defined a query as a function so that we can invoke it anytime.

The next step is to load GPS data from CSV. I’m using my bike computer data that I exported to CSV format earlier.

Download sample CSV file here.

  • In Query Editor, click “New Source” from “Home” tab then click “CSV”

Power BI CSV

  • Browse and open the CSV file
  • Click OK

Power BI Query Editor CSV

  • Click “Add Custom Column” from “Add Column” tab and name it “Geolocation”
  • Enter the following formula to invoke the fn_GeoLocation function and click OK

Power BI Query Editor Add New Column

Note: I used “Number.ToText” function to convert latitude and longitude data type from number to text as we defined the function parameters as text.

  • You’ll get a “Information is required about data privacy” warning message. Click “Continue”

Power BI Query Editor Add New Column 2

  • Select a desired privacy level from the list then click “Save”

Power BI Privacy Levels

Note: Learn more about “Privacy levels” here.

  • Scroll right to find the new added column
  • Expand “Geolocation” column

Power BI Expand Complex Column 2

Voila! We have got all information needed

  • Click “Close & Apply” from “Home” tab from the ribbon and wait until the data loads into the model.

Power BI Query Editor Close & Apply

Note: It might take sometime to load data into the model based on your dataset size. This is because of sending coordinates to Google Maps row by row then loading the results into the model, so the process could be slow if you have a large dataset. There are also some limitations applied to the API usage.

Now you can easily use new columns for different purposes like putting Country or Postal Code in slicers, creating a Location hierarchy and so on. So there are lots of different interesting things you can do with the information we got from Google Maps.

Power BI and Google Maps API

Geo-Location APIs in General

As you know Google is not the only one who provides geo-location APIs. You can do the same using Microsoft Bing Map or HERE Maps.

More learning materials:

Limitations

Using Geocoding API is not free so some limitations apply when you are on a free usage plan. For instance you can use only 2,500 requests per day and 10 requests per second. Have a look at here to learn more about the API usage limitations.

The other alternatives like Microsoft Bing Map and HERE Map have some limitations as well.

For more information have look at the link below:

12 thoughts on “Power BI and Google Maps API (Address Lookup)

  1. Hi. After of saving the model I’m getting this error:

    “The the Column1 from table wasn’t found”

    Also, when I expand the table, it returns 2 formatted_address. Which one should I choose?

    Thank you.

    1. I have the same problem with the missing Clumn1 and I tried to fit it to my table but it is still giving me the same error message 🙁

  2. Great tutorial thank you so much. Is it possible for you to create a similar tutorial for google map direction? I’m struggling a lot with that right now 🙁
    I have done all the steps until visualization in power. I get the correct response from the invoked function as well but I don’t know what to do afterwards and how to visualize it in Power BI

  3. Hi

    Nice tutorial but it seems that Google does not allow anymore to cache Geocoding information that you get from their API.

    Any idea how to work around this ?

    Thanks

      1. Hi Soheil,

        Small correction: I’ve contacted Google and Google does not allow to store distance matrix API results.
        Geocoding (latitude, longitude, site id..) can still be cached but for a limited period of 30 days!

        Cheers

  4. Awesome tutorial. But how to handle exception when street_address does not exist? It throws off the entire function and I cannot ignore errors like I usually can.

    1. Hi Martin,

      First off, thank you so much for checking out the blog and leaving your thoughts! 😊 I’m thrilled you found it helpful.
      I’m actually a bit surprised to hear from you on this on; it’s from way back in March 2019!
      I’d love to hear more about the challenges you’re facing with handling exceptions, but I can’t promise to dive into it right away.
      Life’s been a bit hectic, and I’ll need to find a moment to revisit the solution.

      Your feedback, though, has sparked my curiosity, so if you have the time, share more details about the hurdles you’re encountering. Let’s see if we can figure this out together when I get a chance.
      Thanks again for reaching out!

      Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.