Calculating Travel Distance/Time in Power BI using Bing Map API

[2017-July-24] Map visualization in Power BI has drawn many data professionals into a field of сreating visual data stories with maps. It only takes a dataset of Geo located data points to do this and everything else is managed by map visual controls plus one's imagination. You have a choice to use native maps in Power BI, shape maps, filled maps, flow maps, etc. Here is a link to a recent David Eldersveld article that can give a glimpse into those maps and what you can do with them, 10 Ways to Create Maps in Microsoft Power BI.

However, there is no built-in functionality yet in Power BI that would calculate travel distance and time between Geo points. Let's say, a business owner wants to know how many customers he could deliver his product to within 5 minutes, 10 minutes, time intervals could go on.

In order to solve this problem, we can use Bing Map API resource to geocode addresses, calculate routes, get information about traffic incidents and issues in a specified area and many more things: Bing Maps REST Services.

I was inspired by Dustin Ryan's article (https://sqldusty.com/2016/04/26/power-bi-and-the-bing-maps-api/) where he explored a Geo location API using textual address information. And I went a bit further and used http://dev.virtualearth.net/REST/V1/Routes/Driving? API to calculate a route between two locations, fortunately, it can provide me with a travel time as well.

Here is an XML output from a simple web service request to calculate a route between Boston International Airport (1 Harborside Dr, Boston, MA 02128, USA) and MIT (77 Massachusetts Ave, Cambridge, MA 02139, USA) 



Which then I can replicate as a web based data source in my Power BI data model to return Travel Distance and Duration data elements:



In addition to this, I can create a function in Power BI with 2 parameters, Start and Destinations address and then use it along with my data set:

let 
    Route = (StartAddress as text, DestinationAddres as text) =>
let
    Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0="&StartAddress&"&wp.1="&DestinationAddres &"&avoid=minimizeTolls&key=BingMapsKey")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
    ResourceSets = #"Changed Type"{0}[ResourceSets],
    ResourceSet = ResourceSets{0}[ResourceSet],
    #"Changed Type1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}),
    Resources = #"Changed Type1"{0}[Resources],
    Route = Resources{0}[Route],
    #"Changed Type2" = Table.TransformColumnTypes(Route,{{"Id", type text}, {"DistanceUnit", type text}, {"DurationUnit", type text}, {"TravelDistance", type number}, {"TravelDuration", Int64.Type}, {"TravelDurationTraffic", Int64.Type}, {"TrafficDataUsed", type text}, {"TrafficCongestion", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Id", "BoundingBox", "DistanceUnit", "DurationUnit", "TrafficDataUsed", "TrafficCongestion", "RouteLeg"})
in
    #"Removed Columns"
in 
    Route

With this function, I can then calculate travel metrics from the Boston Airport to 9 other Geo locations using the following dataset:



And then I can get my Travel Distance/Time results in Power BI by calling this new function for each of the dataset's rows:




With those calculations results, I can then place them on my map visualization and use highlighted [Time Duration] slicer to control the visibility of location points depending on their travel distance from the Boston Internation Airport.




I have also created two other M functions in Power BI to help with Geo coding and Address formatting. Feel free to use them in your geo data discovery, but please make sure to update them with your own Bing Maps Key that you're going to get after a registration to use Bing Map resource.

fnAddressLocate function takes textual address as a parameter and returns Latitude and Longitude coordinates for this address location:

let 
    AddressToLocate = (address as text) =>
let
    Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/V1/Locations/"&address&"?o=xml&key=BingMapsKey")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
    ResourceSets = #"Changed Type"{0}[ResourceSets],
    ResourceSet = ResourceSets{0}[ResourceSet],
    #"Changed Type1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}),
    Resources = #"Changed Type1"{0}[Resources],
    Location = Resources{0}[Location],
    #"Changed Type2" = Table.TransformColumnTypes(Location,{{"Name", type text}, {"EntityType", type text}, {"Confidence", type text}, {"MatchCode", type text}}),
    Point = #"Changed Type2"{0}[Point]
in
    Point
in 
    AddressToLocate

fnFormatAddress function can help you to cleanse and format an address for better Geo Location. 
For example, it can change this address line "1 Harborse D, Bostn, 02128" into this formatted/corrected version "1 Harborside Dr, Boston, MA 02128"

let 
    AddressToFormat = (address as text) =>
let
    Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/V1/Locations/"&address&"?o=xml&key=BingMapsKey")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
    ResourceSets = #"Changed Type"{0}[ResourceSets],
    ResourceSet = ResourceSets{0}[ResourceSet],
    #"Changed Type1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}),
    Resources = #"Changed Type1"{0}[Resources],
    Location = Resources{0}[Location],
    #"Changed Type2" = Table.TransformColumnTypes(Location,{{"Name", type text}, {"EntityType", type text}, {"Confidence", type text}, {"MatchCode", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Point", "BoundingBox", "EntityType", "Address", "Confidence", "MatchCode", "GeocodePoint"})
in
    #"Removed Columns"
in 
    AddressToFormat

In overall, I really liked enriching my Power BI  data model with web requests to Bing Map APIs. The only thing that I didn't like was a response time. On a set of 10 records, it took approximately  5 seconds for the refresh, however, I've noticed that on larger datasets it might take more time per one record to call and process web request results.

What can be done to speed up this recalculation process may be discussed in another blog post. But in the meantime, feel free to comment if you like the described approach for Travel Distance/Time calculation in Power BI using Bing Map API.