Geocode an Address List with Google Maps API and Power Query

The YouTube demo of this can be found here.

---

Step 1. Get Your API Key

You will have to create a Google API account to get your API key. Follow these steps to complete the setup.

1. Go to the Google Maps Developers Portal at https://console.developers.google.com

2. Complete the setup process. For this application, we only require the API to access the “Places” API category.

When completed, copy your API key to your clipboard or notepad. Please be careful with how you use your API keys. Both Google and Bing Map 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

In this step, we take a sample line of data and pass it to Power Query.

Next, go to https://developers.google.com/maps/documentation/geocoding/overview and copy the XML template. I prefer to use XML as its easier to use with Power Query. Here is the template code from Google.

https://maps.googleapis.com/maps/api/geocode/xml?address=1600+Amphitheatre+Parkway,
 +Mountain+View,+CA&key=YOUR_API_KEY

We copy the contents from our Location column and paste it into the code after the “address=” section. The template address parts can be removed, and your API key can be paste into the appropriate location.

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 we need to edit the table returned by the query further.

When in the Power Query window, edit the created query name with something more memorable. I chose to call the Query, Google_Loc_API.

We now start the extraction process by expanding the results column and then selecting the address and geometry columns from the table.

And, continue to expand the table until we extract the Latitude and Longitude coordinates.

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

Our sample query is now returning the data we require to geocode an address. Now, we want to apply these steps to all addresses in our input table. To do that, we need to convert our sample query to a Parameter Function.

The first step in the development process is to edit the M code in our sample query. We do this by clicking on the query called Google_Loc_API and clicking on “Advanced Editor” in either the Home or View tabs. In the advanced editor view, we edit the code by declaring the parameter before the Power Query steps, using the sequence.

let
Loc = (Location) =>

And then place the created parameter element (Location) into the M code to replace the currently hardcoded location of “11 Smith St, Alice Springs...” and finish the code with

in
Loc

Our Parameter Function is now complete and looks like this.

In the next step, we use the created parameter function to return the geocode details for our address list.

Step 4. Invoke a Custom Function.

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

1. Select the Input Table.

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

3. Select the “Google_Loc_API” function

4. Change the Location parameter to accept the “Location” of the Input Table as input. And,

5. Then select “Ok”

Our parameter function now makes API calls for each of the addresses of the Input Table. When complete, the Input Table will look like this.

Note, errors are being returned. This occurs when the API is unable to recognise the form of the input as an address. We need to identify what is causing this error because the geocoding won’t proceed past the row that returns the error. Also, a further complication is the “error” value isn’t selectable in the column filter pane. To overcome this, we add another custom column and enter the M function called try() and enter the function column for it to test.

The try function tests the function to see if the query returns records or an error (Haserror). We can filter this column to show those rows that have the value true.

The filtered rows indicate the error is being returned on rows where the prefix “CNR” appears.

We can correct this error by adding a step in our Input to Table to search our Location column for occurrences of the acronym “CNR” and replace it with an empty string. When done, we re-run the query to test if any other errors are returned. If not, we can now extract the data from the returned table as per the illustration below.

If satisfied with the return data, we can click “close and apply” to start the query running against our input table.

Don’t expect this stage to be quick. Our input list is only 100 rows, but, the query took 74 seconds. Nonetheless, our input was 100 rows but our output is 109 rows. When this occurs, it is an indication that the API was unable to identify an accurate location and so it identifies what it believes to be close approximations.

We can see these approximations by filtering the “Store Name” column for duplicate values. Follow these steps to identify duplicate values in Excel.

When we filter the Store Name column for duplicated values, we can see there are 2 locations that the API was unable to accurately identify. If we compare the Location address to the formatted_address returned by the API, we can see the approximations Google was able to find.

As can be seen, the API was unable to find an address at Warren Rd & Morris St, possibly because Warren Rd is concatenated. However, the API was able to find a list of Warren Rd locations in the US and Canada.

Which begs the question of why Gilgandra, NSW, 2827 in Australia is ignored.

Conclusion

Our next blog will be on the use of the Bing Maps API where I will compare the two on Ease of use, Speed and Accuracy.

I hope you find this demonstration helpful, if so please subscribe. I plan on producing other blogs dealing with mapping in Power BI and other "how-to" stuff when using Power BI.

#powerbi #powerquery #googlemaps