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:
What is Bing Maps API and what is it good for?
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:
- Locations - Provides geocode and reverse geocode location data: Find a location based on address or point (latitude/longitude). This endpoint also provides information on local business, points of interest or the ability to search nearby businesses.
- Elevations - Provides elevation information for a set of locations. This includes information on their hight above or below a fixed reference point (e.g. sea level)
- Imagery - This endpoint offers maps and images according to your specifications. This includes static maps as well as display of routes on maps and getting streetside or bird's eye view images.
- Routes - Enables calculation of routes between locations for driving, walking or transit. This includes parameters for calculating best routes for vehicles of special dimensions or routes that are optimal in terms of time or distance.
- Traffic - Provides information on traffic incidents and issues in a specific area at a specific time.
- Autosuggest - Returns a list of autosuggested entities based on a users query (phrase) the user is most likely to search for
- TimeZone - Using this endpoint you can fetch information on time zones for specific locations. It also enables converting UTC datetimes to other time zones even taking into account daylight saving time
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".
How to connect to Bing Maps REST API?
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:
- Create an account or logging in using your existing Microsoft account or Enterprise Azure AD account
- Go to "My account" in the menü bar at the top and klick "My Keys"
- Click the link "click here to create a new key" and fill in the form. Provide a name, leave "Application URL" blank and stick to default for the rest. Click "create" when done.
- Click "Show key" and copy your Bing Maps Key to the clipboard to use it in your code
The basic URL to connect to the Bing Maps services is:
Depending on the endpoint you want to use, this pattern will form the call for one of the options mentioned above. Lets assume you want to connect to the Locations-endpoint. This will enhance the URL as folllows:
As "resourcePath" you might provide a string containing the address of your point of interest. In the "queryParameters" section you can enhance your request by a couple of parameters that depend on the endpoint you use and the task you want to perform. One example might be the parameter "o=xml" which sets the output format to xml.
For example the URL for fetching the geodata information for the address of Microsofts Visitors Center in Redmond, Washington is:
https:// dev.virtualearth.net/REST/v1/Locations/”15010 NE 36th St Building 92, Redmond, WA 98052, United States”?o=xml&key=YourBingMapsKey
Here "YourBingMapsKey" is the place to provide your key.
Fetching geocodes for addresses or vise versa using the Location API is a very useful and frequent application for a service like Bing Maps.
Dustin Ryan did a great job describing how this and fetching information on travel distances and durations is done in detail. So I want to repeat my recommendation on reading his post on this topic ("Power BI and the Bing Maps API"). I also recommend reaching out to the Microsoft Documentation for more examples and details on the capabilities of all the individual APIs.
A step by step example
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 1: Load the list of addresses and the list containing the trip information ("RawData") to the Power Query Window. Do not load it to the model, but keep it as a connection.
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.
This function takes an address as parameter and returns the related geocode from the Bing Maps API. The part of interest here is the Source-step. It uses the Web.Contents-Function to call the Locations endpoint as explained before. As you might have noticed I am using the RelativePath and Query options of the Web.Contents() function to make it easier to build the needed URL and also increase readability following the suggestion by Chris Webb from this blog post: Using The RealtivePath and Query Options With Web.Contents() In Power Query and Power BI M Code.
While the first part reflects the base URL for the needed endpoint, the parameter "relativePath" enables us to enhance that base URL, in this case by the address. Via the second parameter "Query" we can provide additional parameters, e.g. for the output format, which is xml and the necessary API-key.
All other steps are just several steps of expanding the column containing the fetched records via the GUI, till we get the latitudes, longitudes and all the other information we might need.
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!
Greetings from Hamburg, Germany
STAY IN TOUCH...
LATEST ARTICLES ON BI BLOG...