Geocoding with PowerQuery
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:
- 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.
- 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.
To add the geocoding functionality into PowerQuery I'm going to add a function.
Add a New Source as a Blank Query:
then click the Advanced Editor button on the toolbar.
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
Then click back on your query containing the address data
On the toolbar, in the "Add Column" section, click the "Invoke Custom Function" button:
Enter Location as the column name. Select Geocoder as the Function query. Then under ApiKey click the button and select '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.
Then under Address select Column Name and select the address column.
Click OK.
The function will now be called for the rows in your table:
Click the expand button in the new Location column header:
Untick the "Use original column name as prefix" checkbox
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.
Click the Close & Apply button to load the data into your Power BI dataset.
You can now configure Icon Map Pro with your geocoded data: