Using Power Query to Geocode an Address List with Bing Maps API

Steven Doherty
Sep 2, 2020
4 min read

Click here for a YouTube demo of this process.

Executive Summary

There were two reasons I wanted to do these demonstrations. One was to show how Power Query could be used to call API’s while the other was to compare the two. Prior to this, I assumed the API’s to be similar. However, I’ve now concluded there's a big difference. Here’s an overview of my findings.

The obvious question is, which would I choose to use, and the answer is, “it depends”. I would use the Google API if I had a large table to geocode. However, I would want the address list to be reasonably clean before using the API as I have found Identifying and re-working, incorrectly identified addresses, tedious with the Google API.

The Bing API is slower in comparison, but, the amount of information returned on an address (not just Latitude and Longitude) is far greater, and the inclusion of a reliability measure makes the proofing step so much easier.

In the following sections, we discuss the steps for geocoding an address list with Power Query.

Step 1. Get Your API Key

You will need to create a Bing Maps API account to get your API key. Follow these steps to complete the setup.

1. Go to the Bing Maps Portal at https://www.bingmapsportal.com/

2. You can sign in with an Office 365 or Windows account.

When complete, copy your API key to your clipboard or notepad. Be careful with how you use your API keys. Both Google and Bing Map’s API’s are, “pay for use”, so if you use it a lot or your key is misused by someone else, you may find unwelcome charges to your credit card.

Step 2. Create the Power Query Sample

Many of the steps shown here are similar to those used in the Google API demonstration.

As with the Google API, we copy a sample line of data and paste it to a new sheet in Excel.

Next, go to https://docs.microsoft.com/en-us/bingmaps/rest-services/locations/find-a-location-by-address and copy the default template. In the Youtube video, I edit the default code to change the output to XML, The code snippet below is the edited version I use in the demonstration.

http://dev.virtualearth.net/REST/v1/Locations?countryRegion={countryRegion}&adminDistrict={adminDistrict}&locality={locality}&postalCode={postalCode}&addressLine={addressLine}&o=xml&key={BingMapsKey}

Unlike the Google API, the Bing API has multiple points of input for the address parts. The definition for each of the API search parts is here. In this illustration, I show how I placed the address parts in the API URL. Copy and Paste your API key in the appropriate URL part.

Next, Copy the completed URL and then on the menu bar click “Data” and “From Web” and paste the URL into the dialogue box and click “ok”.

When the Power Query window opens click “Transform” as further editing of the table returned by the query is required.

When in Power Query window, edit the newly created query with a more memorable name like “Bing API”.

We are to start the data extraction process by removing all columns apart from the “Recordsets” column.

We begin the extraction process by expanding the “Resourcesets” Table. Continue to expand the tables until “Address”, “Confidence” and “GeocodePoint” are returned.

Finally, expand the Address and GeocodePoint Tables and extract the following columns.

There is an additional step required with the Bing API. Notice in the fields returned there’s one called “Usage”. This field returns two values, Route and Display. We add a step of filtering for the “Display” value as it is better suited for Power BI mapping purposes.

Step 3. Convert the sample query to a Power Query Parameter Function

Now that we have the steps we require, we convert this sample query into a Power Query Parameter Function.

Step one is to edit the M code in the sample query. We do this by right mouse clicking on the query called Bing API and then clicking on “Advanced Editor”. In the advanced editor view, we edit the code by declaring the parameter before the Power Query steps and using the sequence.

let
Location = (State as text, Suburb as text, Pcode as text, Address as text) =>

We then replace the URL parts with the parameter elements of State, Suburb, Pcode and Address into the M code, replacing the required elements as per below.

Ensuring we end the M code with;

in
Location

Our Parameter Function is now complete and looks like this.

Next, we invoke the Parameter Function.

Step 4. Invoke the Function.

We have our table of addresses, and we have our function with all the steps needed to return the geocode details. Let’s now put them together.

1. Select the Input Table.

2. Select “Add Columns” followed by “invoke Custom Function”.

3. Select the “Bing API” function

4. Unlike the Google API, The Bing API has multiple parameters requiring a column assignment. See the illustration below on how we do this. And,

5. Then select “Done.”

Our parameter function now runs and makes an API call for each address in the Input Table and looks like this.

As the final step, we expand the Bing API column and click on the columns we want to be returned.

We can click “close and apply” to start the query running against our input table.

This is the stage that we notice the difference between Google and Bing’s API. Whereas, Googles API returned the output table in 74 seconds Bing’s takes 2 minutes and 50 seconds.

However, one of the things I like about the Bing API is the inclusion of the “Confidence” column, which allows you to quickly filter to “Low” confidence addresses to identify their issues.

In addition, we can also employ the technique of highlighting duplicate values.

Filtering the “Store Name” column for duplicate values reveals 3 locations where Bing provided approximations. As with Google if we compare the Input address, to the “FormattedAddress” returned by the API, we can see the estimates Bing used to formulate a location.

What I like about the Bing approximations is it easier to see what parts are matched, as they’re in the FormattedAddress, as opposed to those parts, not in the FormatedAddress, Which Bing couldn’t find a match for. I find this easier for debugging and correcting the input address to help return a better result.