This week I was working on a project for a school up North. This customer wanted to use Power BI and map visualizations to view the locations of the various school buildings in the district. The problem for this very large school district was that they were missing the street address information for some of the buildings and for other buildings they were missing the latitude and longitude.
New to Power BI? Start with these 5 tips!
The good news is that Power Query in Power BI is flexible enough for us to take advantage of the Bing Maps API so that we can lookup the missing pieces of information we need. In this blog post, I’m going to show you how you can use the Bing Maps API to look up an address based on a latitude and longitude or use a street address to find a latitude and longitude for the location.
Getting Started with Bing Maps API
To begin using the Bing Maps API, I used the following steps:
Step 1: Sign up for a free Bing Maps developer account: https://www.bingmapsportal.com
Step 2: Once you have your Bing Maps dev account, you need to create a new key. This key will allow you to use the Bing Maps API. On www.bingmapsportal.com, select My account and select My keys:
Step 3: Select the hyperlink where it says “Click here to create a new key.” Fill out the form (you can leave the Application URL section empty) and click the Create button.
Step 4: Once you’ve created the key, you’ll want to copy the key to your clipboard so you can use it in your Power BI query when you access the Bing Maps API.
Get a Latitude Longitude Point for an Address
With the Bing Maps API, there’s a lot of different things we can do to enhance our datasets in Power BI. The first thing I’d like to show you is how we can find a point for a given address.
In this particular example, I have a list of addresses for the stadiums of the 32 NFL teams.
I want to look up the latitude and longitude for each of these addresses. To do this, we’ll need to create a new query in Power BI that we’ll use as a function to request the lat and long from Bing Maps for each address.
There’s a lot of documentation and examples that we can use. You can read more about it here: https://msdn.microsoft.com/en-us/library/ff701714.aspx
The URL example I’m using is the following (don’t forget to replace “BingMapsKey” with your actual key you should have retrieved in the previous steps):
http://dev.virtualearth.net/REST/v1/Locations/400%20college%20drive%20middleburg%20florida%2032068?o=xml&key=BingMapsKey
Notice the o parameter (output) in the URL above. This specifies the output as XML, which will help us when returning the lat and long values of the point.
Use the following steps to create a function in Power BI that we can use to find the lat and long for each address:
Step 1: Create a new query in Power BI with a Web source.
Paste in the URL above and be sure to use your Bing Maps key.
Click OK.
Step 2: When you click OK, you should see results similar to this:
Scroll to the right and click the Table link.
Keep clicking the Table link and drilling through the record set until you reach where you can see the address and the attributes. Click Table under Point. 
This will display the latitude and longitude for the address (which is the address of Pragmatic Works where I used to work and where many of my friends work).
Step 3: Now we need to use this query to create a dynamic function that we can use to look up the point information for each address. Click the Advanced Editor button in the Home ribbon. You should see the M query:
We are going to tweak this query into a function as seen here:
I’ve highlighted the changes to the query so you can see how I created the function and parameterized the URL with the address variable. Click Done.
When you click Done you should see your function in the Query Editor window.
By default, the new query will be named Query1, but you may choose to give the new query a more meaningful name, like fxPointLookup.
You can test the function by clicking the Invoke button and keying in an address. Don’t forget to delete the extra step from the query if you invoke the function to test.
Step 4: Now we’re ready to use our new function in a custom column in our query that has the addresses.
In the original query with the address, go to the Add Column ribbon and click Add Custom Column.
In the custom column, we’re going to use our new function and the column in the original query that contains the address, as seen here:
The only thing I’ll point out here with this formula is that I’m replacing the “&” with “and” so that the URL query string is not broken. You may or may not choose to do this.
Click OK when you’re finished. After clicking OK, you’ll have to address some privacy dialogue boxes that will appear. Once you’ve address the dialogues regarding privacy, click the little expand button in the top right of the new column.
Click OK. And now you have the Latitude and Longitude for your addresses!
Get an Address for a Point
We can use the above steps to find a Lat and Long for an address, but what if we want to find an address for a Lat and Long?
Step 1: We can use the following query as a template:
http://dev.virtualearth.net/REST/v1/Locations/47.64054,-122.12934?o=xml&key=BingMapsKey
Create a new query using the URL as a source just like we did before. Navigate down through the results until you find the Address.
Step 2: Create a function using the Advanced Editor just like before, except this time we need to pass two variables, as seen here:
Click OK.
Step 3: Create a new custom column in the query that contains your Lat and Long points, as seen below. Click OK when you’re done.
Keep in mind that you may need to convert your Lat and Long values to text if you run into a data type error.
Step 4: Expand the new column and select the fields you wish to display in the query. Click OK.
You should see similar results as seen here:
Now we’re ready to visualize our data on a map in Power BI!
Don’t forget to set the Data Category values for your fields that contain geographic data!
Resources:
Go to Bing Maps Portal to get signed up for your free dev account and to find your API key: https://www.bingmapsportal.com
Here’s documentation on getting an address for a specific point: https://msdn.microsoft.com/en-us/library/ff701710.aspx
Or getting a point for a given address: https://msdn.microsoft.com/en-us/library/ff701714.aspx
Feedback?
I hope you found this blog post useful. If you did, feel free to share it! As always, feel free leave a comment down below to share your thoughts with me. Thanks for reading!
