In last week’s post I described the new Power Query M functions for working with Well Known Text objects, and in a post a few weeks ago I showed how you can use the Icon Map custom visual to display Well Known Text data. In this post I’ll show you how you can put all this together to do something really useful: display routes on a map in a Power BI report.
First of all you’ll need to download the latest version of the Icon Map custom visual here, because at the time of writing the version in AppSource doesn’t have the WKT functionality needed. Second, you’ll need a data source with a table of latitudes and longitudes that represent points on a route that you want to plot. I’m going to use the Azure Maps Get Route Directions API as my data source; to keep things simple I’m going to use the Shared Key authentication method for this API, but I’ll explain below why this can be a problem in the real world. To follow the example you’ll need to create an Azure Maps Account in the Azure Portal so you can get your own key.
Here’s a simple example to start off with. The following M code:
let
Source = Json.Document(
Web.Contents(
"https://atlas.microsoft.com/route/directions/json",
[ApiKeyName = "subscription-key", Query
= [
#"api-version" = "1.0",
query
= "51.502243,-0.140073:51.517777,-0.138621",
travelMode = "pedestrian"
]]
)
),
routes = Source[routes],
routes1 = routes{0},
legs = routes1[legs],
legs1 = legs{0},
points = legs1[points],
PointsList = List.Transform(
points,
each GeographyPoint.From(
[longitude],
[latitude]
)
),
LineRecord = [Kind = "LINESTRING", Points
= PointsList],
WKTLineString = Geography.ToWellKnownText(
LineRecord
),
ToTable = #table(
type table[
Category = number,
WKT = text,
Size = number
],
{{1, WKTLineString, 1}}
)
in
ToTable
…calls the Azure Maps Get Route Directions API to get a walking route between two locations in central London. When you run the query you’ll be prompted to authenticate – choose the Web API option and enter your key. It’s a very basic example of how the API can be used: I have assumed there’s only one leg in the route, then taken the list of latitudes and longitudes for that single leg, turned that into a list of points and then turned that list into a linestring as I showed in last week’s blog post. Here’s the output in the Icon Map visual:
This is what the table returned by the query looks like:
The Category and Size columns aren’t very interesting; Icon Map needs these columns to display data. The WKT column contains the linestring object. Here’s how the visual is set up in Power BI:
We can do better than this though. In the example pbix file I’ve uploaded here, I’ve created a more sophisticated query that asks for six different routes between the two locations used above that all go via one waypoint (so there are two legs in the route data returned). I won’t go through the M code because there’s nothing interesting in it, but the end result is a report that allows you to plot these six different routes in different colours on a map and use a slicer to hide and show them, along with some information on their length and travel time:
One last thing to point out regarding these examples is that they won’t refresh if you publish them to the Power BI Service because I’ve used the ApiKeyName option in Web.Contents to handle key authentication (see here) and that isn’t supported in the Service yet. To work around this you’ll need to hard code the Azure Maps API key in your query and use the Skip Test Connection option in the Power BI Service.
Awesome! Thanks for sharing!
But what about markers? For example, I’m just curious where the route was started, and want to add some arrow icons…
And there are some limitations on WKT field length in PQ, right?
I m facing the same issue, did you find any solution to add more information to the tooltips?
Awesome Appreciate your effort
When I added the preivious code in my environment i got the below error
Expression.Error: The name ‘Geography.ToWellKnownText’ wasn’t recognized. Make sure it’s spelled correctly.
any recommendations ?
Thanks for sharing this great visualisation option!
I loaded a properly sorted logistics excel file with tours and stop points into Power BI, added a slicer for the selection of the tour to display – and was surprised to see a zigzag line showing me that the order of the stop points was lost when preparing the linestring via
CONCATENATEX(
ALLSELECTED(‘Data’),
[LONGITUDE] & ” ” & [LATITUDE],
“, ”
There seems to be no sorting available within in a DAX measure. Any ideas how to overcome this issue which might affect similar use cases?
You need to use the option OrderBy_Expression parameter in ConcatenateX to make sure your stops are ordered correctly. See https://dax.guide/concatenatex/#
Perfect – Thanks a lot!
Hi Chris. Great detailed tutorial. I would like some help, though if possible. I’m trying to project water supply lines over the map but I’m unable to project them.
For example, this linestring: 40.8743729401833 -8.67038108669452 40.8743970461289 -8.67038806819007 Are you able to project this in icon map visual? Thank you in advance.
really nice, But how can I create a function in my power bi quary editor to take all the data from a column for lat and another for long instead of only one lat and long?
I mean I dont want to hard code the lat and long but have a function that fits to my table with colums for lat and long
Brilliant demo, thank you! I’m trying to leverage this and trying to invoke the list functions in a table but having some difficulties (still learning PQ/M).
I succeeded to get the api call in the table for each row like so:
AzureMapsRoutes = Table.AddColumn(#"Changed Type1", "Routes", each Json.Document(Web.Contents("https://atlas.microsoft.com/route/directions/json?subscription-key=SKA_primary_key", [ Query=[#"api-version"="1.0", query=[start_latitude]&","&[start_longitude]&":"&[end_latitude]&","&[end_longitude], travelMode="car", maxAlternatives="5"]])))
But now I can’t figure out how to invoke the function (CreateWKTLineRecord) to each record in that new column. Any tips? Thanks
Figured it out 🙂
AzureMapsRoutes = Table.AddColumn(#"Changed Type1", "Routes", each Json.Document(Web.Contents("https://atlas.microsoft.com/route/directions/json?subscription-key=SKA_primary_key", [ Query=[#"api-version"="1.0", query=[start_latitude]&","&[start_longitude]&":"&[end_latitude]&","&[end_longitude], travelMode="car", maxAlternatives="5"]])))
AzureMapsWKT = Table.AddColumn(AzureMapsRoutes, "Routes_each", each List.Transform(AzureMapsRoutes{_}[Routes][routes], each Record.AddField([summary], "WKT",CreateWKTMultiLineString([legs])))), #"Expanded Routes_each" = Table.ExpandListColumn(AzureMapsWKT, "Routes_each")
etc...