Have you ever received a dataset with addresses, only to realize that most Power BI Map visuals are optimized for data including latitude and longitude. This happened to me recently, and I wanted to step through a quick solution for how to convert addresses to Lat / Long coordinates in Power Query.

Here’s a sample Excel file that has been imported into Power BI – containing addresses for Florida based Home Depot stores.

Step 1: Create a Bing Maps API Key

Before we can use any API – we typically need to register an account and generate some kind of authentication key. This is true for Bing Maps – and can be done in the Bing Maps Dev Center (linked here:

After signing in – we’ll see a screen that enables us to access our account info – and we want to specifically look for “My Keys”

Then create a new key

Then in the following menu – let’s fill in our key information

Once we hit create – the Key information will populate to our account. From here we can copy it using the dialog box.

Step 2: Retrieve the Bing Maps API URL

There’s a ton of documentation around the format of these API calls – and it’s principally located here.

The full code looks something like this:

http://dev.virtualearth.net/REST/v1/Locations?countryRegion={countryRegion}&adminDistrict={adminDistrict}&locality={locality}&postalCode={postalCode}&addressLine={addressLine}&userLocation={userLocation}&userIp={userIp}&usermapView={usermapView}&includeNeighborhood={includeNeighborhood}&maxResults={maxResults}&key={BingMapsKey}

There are a LOT of API parameters included in this code, which are the elements wrapped in the squiggly brackets. We only need the elements that are relevant to our specific use case which are:

  • countryRegion={countryRegion}
  • adminDistrict={adminDistrict}
  • locality={locality}
  • postalCode={postalCode}
  • addressLine={addressLine}
  • key={BingMapsKey}

The rest we can get rid of. So our modified code looks something like this: 

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

Step 3: Build the API Web call in Power Query

For this API call to work across a bunch of addresses in our table – we need to use Power Query Parameters and a Custom Function to ultimately feed the appropriate value of each row into the appropriate API parameter.

We can do this by creating a parameter for each of the columns in our table that will serve as inputs for our API call. As we create each parameter – we need to set a default value – which can easily be the values from the first row of our data table.

Now rinse and repeat for the other location specific API elements in our table:

The next step is to open the Web Import data tool – and flip the radio button to “Advanced”. We can use this window to break down our URL into chunks and feed each parameters into the appropriate part.

In our case – since the API Key and Country Code will be the same for all entries – we can ‘hard-code’ those values into the script – rather than using a parameter.

If done correctly (with no typos) – the query will kick off and return some JSON code that’s been converted to a table and expanded down a few levels: Here’s what that initial result looks like.

Step 4: Create a Custom Function with API Query

We can find the Latitude and Longitude results from our original query by clicking on cell of the column labeled resourceSets.resources.point.coordinates

Let’s first remove all the other columns from this results set

Now we can ‘extract’ the lat / long values from our list by clicking on the arrows of our single cell row and selecting “Extract Values”

Set the delimiter to comma

And then we have our coordinates in the same cell

To create the custom function – now we right click on this query that we just finished optimizing and select Create Function

Let’s give our function a name – and if done correctly – we should also the 4 parameters listed with it.

Once created – the function and parameters will be moved into their own query folder

Step 5: Invoke Custom Function by Adding a New Column to Address Table

Before we can use the function on our address table – we have to ensure that all of our columns have a matching data type to their parameter. In our case – all of our parameters were ‘text’.

Since our source was a quickly assembled Excel file – the Zip column was actually imported as a number, so we’ll need to change to over to text.

Now we can add a new column that invokes the custom function

In the window that appears, we can select our parameters and click OK.

Of course then we immediately get an error message saying “Formula.Firewall: Query ‘Home Depot Addresses’ (step ‘Invoked Custom Function’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination”

We can fix this by opening the Options Menu in Power BI Desktop and changing the Privacy setting for the Current File

Now our query (if selected again in the query steps) will produce results

Now just expand the Table column to show the individual values for each row.

Then split the column based on the comma delimiter and rename each to Lat and Long

And just like that – we now have values that can be dropped into any Power BI Map visual:

Now it should be worth noting – that getting lat and long values via API (as we have done in this exercise) – will call the API every single time the dataset is refreshed. Since these values are not going to change – it may be redundant to have this step executed over and over and over again. In that case – it might be fruitful to use the resulting table from this exercise as the source for a new report.