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
