Skip to main content

One post tagged with "geocoding"

View All Tags

Geocoding with PowerQuery

· 3 min read
James Dales
Co-founder of Tekantis

Unlike the AzureMaps and ArcGIS Power BI visuals, Icon Map Pro doesn't have the ability to geocode data - to take a postal address or placename and turn it into a longitude and latitude to display on the map. This was an intentional decision for a number of reasons:

  1. Doing this on the fly creates a performance hit before displaying the data, and it has to be done for each user browsing the map.
  2. For security - many of our customers don't want the visual to be able to process data remotely.

A better approach is to geocode the data up front. You can then control where this process happens, and do it once as part of the data ingestion, meaning there is no delay or workload duplication.

Here's a guide to using the Azure Maps geocoding API in PowerQuery to convert addresses to longitude and latitude coordinates.

In this example I have a table of London hospitals and their addresses.

alt text

To add the geocoding functionality into PowerQuery I'm going to add a function.

Add a New Source as a Blank Query:

alt text

then click the Advanced Editor button on the toolbar.

alt text

alt text

Replace the default contents with this M code:

let
// Function to call the Azure Maps Geocoding API
CallAzureMapsGeocodeApi = (ApiKey as text, Address as text) as record =>
let
// Define the API endpoint
ApiUrl = "https://atlas.microsoft.com/search/address/json?api-version=1.0&subscription-key=" & ApiKey & "&query=" & Uri.EscapeDataString(Address),

// Make the GET request
Response = Web.Contents(ApiUrl),

// Parse the JSON response
JsonResponse = Json.Document(Response),

// Extract the first result
FirstResult = if List.Count(JsonResponse[results]) > 0 then JsonResponse[results]{0} else null,

// Extract the latitude and longitude
Coordinates = if FirstResult <> null then FirstResult[position] else null,

// Return a record with latitude and longitude
Result = if Coordinates <> null then [Longitude = Coordinates[lon], Latitude = Coordinates[lat]] else [Longitude = null, Latitude = null]
in
Result
in
CallAzureMapsGeocodeApi

Rename your query to Geocoder

alt text

Then click back on your query containing the address data

alt text

On the toolbar, in the "Add Column" section, click the "Invoke Custom Function" button:

alt text

Enter Location as the column name. Select Geocoder as the Function query. Then under ApiKey click the button and select 'Text'.

alt text

Enter your Azure Maps API Key. This is shown as the "Primary Key" in the "Authentication" section within the "Settings" section of your Azure Maps account.

alt text

Then under Address select Column Name and select the address column.

alt text

Click OK.

The function will now be called for the rows in your table:

alt text

Click the expand button in the new Location column header:

alt text

Untick the "Use original column name as prefix" checkbox

alt text

And click ok.

You should now have Longitude and Latitude columns in your table.

Highlight the two columns and change the Data Type to Decimal Number.

alt text

Click the Close & Apply button to load the data into your Power BI dataset.

alt text

You can now configure Icon Map Pro with your geocoded data:

alt text