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.

Comments

  1. Thank you for post. I struggled a lot for calculating distances. Thanks a ton :)

    ReplyDelete
  2. Hi I am currently struggling to integrate this function but on the basis of multiple cities and not having to have one origin of city rather than create a different starting and ending location and then integrate them on to the table . could you provide me some solution as I was not able to find on internet

    ReplyDelete
    Replies
    1. What solution are you trying to find on Internet?

      Delete
  3. Thank you so much. Can you also show routes instead of points?

    ReplyDelete
    Replies
    1. Check this https://docs.microsoft.com/en-us/bingmaps/rest-services/examples/driving-route-example if you can build a route between two locations. Those locations can be described as text. If this doesn't work, then try get Geo Coordinates from locations first and use them in routing.

      Delete
  4. I am using Bing API in Power BI, but unfortunately I am getting an error as Expression.Error: Access to the resource is forbidden. when I am connected to web. Can you help me

    ReplyDelete
    Replies
    1. Try to access this resource (https://www.bingmapsportal.com/) and use some of their samples there to troubleshoot your access to the Map Rest API service first, and then try to access it in Power BI using web service data query.

      Delete
  5. What does the syntax look like when you use the function to add a new column to your original data set? I understand how Dustin did it in his address example, but I'm unsure of how to do it with both an origin and destination input.

    ReplyDelete
    Replies
    1. This will be done with a help of two separate columns in your data model

      Delete
  6. This has been so helpful!
    Have you posted how to speed up the recalculation process? It is painfully slow
    Thank you!

    ReplyDelete
    Replies
    1. One way to speed things up, I would think, would be to pre-calculate all the distance values and then use them in the reports.
      Using Function way for each geo-location in Power Query is still slow, I agree with you, Nathan.

      Delete
  7. I got the lat and long for the location. But, I would like to understand if we can we calculate the drive distance?

    ReplyDelete
    Replies
    1. Yes, you can. The Rest API provides you with this type of output calculated results: https://learn.microsoft.com/en-us/bingmaps/rest-services/examples/driving-route-example?redirectedfrom=MSDN

      Delete
  8. This is great! I have used it to calculate difference in distance between driving and public transport but was wondering if you are aware of a method to split the 'Transit' into it's components such as train and bus?

    ReplyDelete
    Replies
    1. Sorry for the delay. I think the options to choose between different types of transit would have to be done via parsing the actual output from the API. Because sometimes there might be a transit output may contain a route the will include both bus and trains together. The interesting this that I've just discovered the Bing Map API now offers route options for trucks: https://learn.microsoft.com/en-us/bingmaps/rest-services/routes/calculate-a-truck-route

      Delete
  9. Could you share the dataset for pratice?

    ReplyDelete
    Replies
    1. Good question, I don't remember the actual origin of the Boston area dataset. The blog post was written 6 years ago and it may take some time to find the pbix file with the actual dataset to trace its origin. What I would suggest, find an open dataset with the street address list and then make your destination column with one constant street address value to test the Geo Mapping. I hope this helps.

      Delete

Post a Comment