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:

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:

Leave a Reply