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.

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

This can be done from Query Editor in M language.

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.

• Open your web browser and go to Google Maps Geocoding API
• Sign into your Google account
• Click “Get a Key”

• Scroll down and click “Get A Key”

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

• Type in a name then click “Create”
• 

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.

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

• Stick with the default and click “Connect”

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

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

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

• Expand the “types” column as well

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

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

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

• Click “Use First Row As Headers”

• Rename the query then save the model

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”

• Change the code as per screenshot below

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”

• 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

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

• 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”

• Browse and open the CSV file
• Click OK

• 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

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”

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

Note: Learn more about “Privacy levels” here.

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

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.

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.

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:

• Get Bing Maps key
• Microsoft Bin Maps REST API
• Get HERE Map API
• HERE Maps Geocoder API

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:

• HERE Maps Plans
• Bing Maps Licensing Options