Reverse Geo Coding Using Google Maps API In Power Query

Overview 

 

I work in a safety team, where I am working on a report for safety analysis.

 

Our technicians have to fill pre-start forms before starting any work. My aim is to create Site location (work location) vs Record Location (GPS location when the form is completed) report.

 

It is important as we want to confirm if technicians are filling and completing the forms on Site Location.

 

When technicians submit their form, GPS location coordinates are saved automatically. My aim is to get the address out of the coordinates.

 

I am going to use Excel Power query (M-quey), google maps API to get the location from coordinates.

 

Below is the table that I am going to use for generating Site Location vs GPS Record location to know from where the form is submitted.

 

Below is the format of Record Location that I get from the system. I used Excel formulas to get Lat and Long.

 

 

I have used Reversed Geocoding in Google maps API to get an address. For using Google Maps Api we need API key.

 

How to get Google Map API Key - https://developers.google.com/maps/documentation/javascript/get-api-key

 

The first step is to get data in Data table, select the entire table and name the table.

 

 

Name the table and click Ok

 

 

Click on Data tab and click From Table

 

 

Below is the table that we get, click on Advanced editor.

 

 

Click close and load, you can see the Data table under workbook queries in the right hand side.

 

 

Click on Data Tab, click From other Sources => Blank query, in this query we are going to get Location from coordinates using Reverse Geocoding.

 

 

Click on Advanced editor, and paste the below code.

let
Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"Address", type text}, {"Record Location", type text}, {"Lat", type text}, {"Long", type text}}),
get_geo = (lat as text, lng as text) =>
let
Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/geocode/json?latlng="&lat&","&lng&"&key=APIKey")),
results = Source[results],
#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"formatted_address", "types"}, {"Column1.formatted_address", "Column1.types"}),
#"Expanded Column1.types" = Table.ExpandListColumn(#"Expanded Column1", "Column1.types"),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column1.types", each ([Column1.types] = "street_address") or ([Column1.types] = "establishment")
or ([Column1.types] = "Locality") or ([Column1.types] = "route")),
#"Transposed Table" = Table.Transpose(#"Filtered Rows"),
#"Reversed Rows" = Table.ReverseRows(#"Transposed Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true])
in
#"Promoted Headers"
in
get_geo

 

Click Done.

 

 

Now you can see two queries, one for the table and other query is a function for Reverse Geocoding.

 

Click on JSADataTable.

 

 

Make sure you convert Data type of Lat and Long column to Text

 

 

Click on Invoke Custom Function.

 

Click Ok.

 

Now expand Table, and select all options.

 

 

Click Ok, below is the list of columns that we just added to the table.

 

 

If you want to move columns, you could do that as shown below.

 

 

Click home, and Save and Load to a sheet you want.

 

 

Note

I am using the free version of Google Maps.