Category: SSRS

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”

• 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

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”

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

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.

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