Fetching geodata information using Bing Maps API in Power BI

Sometimes you might have to perform analysis based on geographical information. Be it that you have to calculate the distance or travel duration between two points, you need to display some points of interest on a map or simply the need to fetch information such as latitude or longitude for an address to use it in a geospatial visualisation. This is where the web datasource in Power Query comes in handy. Here is how this can be done utilizing Bing Maps API:

Bing Maps is an online map service from Microsoft through which various spatial data can be viewed and spatial services can be used. Maps, aerial photos and satellite images can be viewed free of charge. A special feature is oblique aerial images from a bird's eye view, which is usually only available of urban areas. 3D models are available for various cities. In addition to the data provided, various services can be used, including location search and route planning.

Bing maps provides a couple of endpoints via REST API. At the time of writing those are:

Of course available APIs and their capabilities may be subject to changes. To stay up to date please reach out to the Microsoft documentation "Bing Maps REST Service".

In order to use Bing Maps API Services you need a Bing Maps Key. You can receive it from your Bing Maps Developer Account by signing in or logging in on www.bingmapsportal.com. Dustin Ryan gives a good walkthrough on how to create the key in his article on "Power BI and the Bing Maps API". Here is a short recap of the steps:

Lets assume your company follows an initiative for carbon neutrality and therefor is interested in the estimated CO2 emissions that their employees cause when traveling by taxi. Fortunatelly your company uses a platform like FreeNow to book all its taxi rides. In this case you are able to get some data similar to the following to base your calculation on:

You get a list of all taxi rides containing addresses of starting points and destinations as well as dates and travel fees. But unfortunatelly there are a lot of information missing to estimate the carbon footprint of that trips such as travel distances and durations. This is how Bing Maps API can help you out: 

Step 2: In order to enhance the list of addresses with the corresponding geocodes we can create the function "fnFetchGeocodes" to query the codes for each address in the list.

Step 3: Join the geocodes from the list of addresses to your list of trips in the RawData query using the merge functionality of Power Query. You have to merge it two times: First for the TourStartID to provide the latitude and longitude for the origin. And second for the TourEndID to provide it for the destination. After that you can simply expand the latitude and longitude information for your origin and destination locations in the RawData query.

Step 4: Create the function "fnFetchTimeDistance" to get information about the distance in km and the duration in minutes between start and end of each taxi trip based on the respective latitude and longitude:

Again, the most important part of this function is the Source-step. This time we are using the DistanceMatrix of the Routes endpoint in the URL to enable calculation of distances and durations. Besides our well known parameters for the output format and the API key we also provide some specification on how to determine the needed information like the "travelMode", units for time and distance and the geocodes for origin and destination for the current row in the RawData query.

Of course I would suggest to persist the results of those queries to some kind of datastore like e.g. SQL Server. In that way you can query the API only for new addresses and trips and not for each and every location in your RawData as I did for this little example. This would speed up the refresh process drastically because querying the API for a lot of locations takes quite some time.

This is how the resultset looks like:

At least now we have everything we need to report on the estimated time and distances our employees traveled by taxi. Multipliing this by an average CO2 emission per km for cars in Europe will enable us to get a feeling on the carbon footprint of our company. We could simply load the data to the model, build our measures and create the report.

But just for the fun of playing around with the Bing Maps API I wanted to add some visual impression of the trips that were done, their starting points and destination. So I went for the Imagery endpoint which offers maps, images and street side- and bird's eye-views of locations. So I added three additional custom columns to the RawData before loading it to the model:

Step 5: Creating two custom columns (BirdViewStart and BirdViewEnd),one for the origin and one for the destination, both constructing the necessary URL to receive a bird's eye impression of the respective location:

Those URLs use the latitudes and longitudes for the start- and end-points of each trip and of course the necessary API key. All other parameters used are just for configuring the angle and size of the picture that is returned by Bing Maps.

Step 6: Creating a custom column (Routes) containing the URL to receive a map with the route from start to end displayed:

Besides the obvious parameters like the geocodes for the origin and the destination and the API key this URL also contains the "travelMode" which is set to "driving" to get the shortest route by car. 

With all this information in place we can create the following report about our estimated travel activities and emissions:

For display of the bird's eye views of start and end and the travel route I downloaded the "Simple Image" custom visual from the App Store and simply dumped the URLs from the "BirdViewStart", "BirdViewEnd" and "Routes" column into them.

If you want to take a closer look on the code for the Bing Maps calls or play around a bit with the sample data on your own feel free to download the following files:

Whenever you need to enhance your data with geographical information Bing Maps API can help you out. Although some services are not available for all countries (e.g. street side views are disabled for Germany) Bing maps offers a wide range of endpoints providing information such as geocodes, local points of interest, elevations, traffic incidents or determining optimal routes according to specific criteria. It also provides images and maps that enable you to add some additional visual impressions of your data.

Key to this is creating a Bing Maps API Key which can be done using your Bing Maps Developer Account on www.bingmapsportal.com.

I hope you enjoyed this article and will be back for my next post. You can also visit my YouTube-Channel for more content on Power BI, SSAS, Power Pivot, Power Query, DAX und M!