PowerBI: How to Get Address by Passing Existing Coordinates to Google Maps Reverse Geocoding API

Create a Function from an Existing Query (Address Lookup)

From the previous tutorial, 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 the screenshot below ===> “Sample Data single load”
let
  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&"
&ampAIzaSyBwKQ7VSYAw4zE0zTtMCQ-k839qoeNdeso")),
 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] = "postal_code" or [Column1.types] = "street_address")),
 #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
 #"Reversed Rows" = Table.ReverseRows(#"Transposed Table"),
 #"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true]),
 #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"street_address", type text}, {"postal_code", type text}})
in
 #"Changed Type"
in
  get_geo

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. —> “Sample Data single load”

  • 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
  • “Sample Data single load”

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.

http://biinsight.com/power-bi-and-google-maps-api-address-lookup/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s