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.