Reverse GeoLocation in Power BI

While I was working on KAISPE Agricultural Farming Application, one of the requirements was to use Power BI and map visualizations to view the locations of my IoT devices. The problem of my application was that I was missing the readable address information for devices, as I already had address points (latitude, longitude) in my database. I also used a report level filter with all locations for my devices using reverse geocoding.

So today I am going to reverse geocode using address points. In my case I am using Bing Maps API to get readable address information.

First Sign up Bing Maps developer account and when you have your Bing Maps developer account, you need to create a new key. This key will allow you to use the Bing Maps API, select My account and select My keys:

1 - Reverse GeoLocation in Power BI

Next fill out the form and you can leave the Application URL section empty and click the Create button.

2 - Reverse GeoLocation in Power BI

When you have created the key, copy the key to your clipboard so you can use it in your Power BI query when you access the Bing Maps

3 - Reverse GeoLocation in Power BI

So, for this particular problem, we have a list of address points for the devices.

7 - Reverse GeoLocation in Power BI

Now, we need to convert the address points (latitude and longitude) into readable address. To do this, we will need to create a new query in Power BI that we will use as a function to reverse geocode.

For this, I’m using the following URL:

http://dev.virtualearth.net/REST/v1/Locations/47.64054,-122.12934?o=xml&key=BingMapsKey

Note: I have replaced the API key which I have created in the last step.

Now we need to create a function using Bing map API key. To do this we will be following these steps:

First, select Get Data in the Home ribbon and Web as source

5 - Reverse GeoLocation in Power BI

Find address column and click the Table link and drilling through.

4 - Reverse GeoLocation in Power BI

Now we need to use this query to create a dynamic function that we can use to convert the address point information. Click the Advanced Editor button in the Home ribbon. You should see the M query

8 - Reverse GeoLocation in Power BI

Now we’re ready to use our new function in a custom column in our query that has the address points.

Now, go to the Add Column ribbon and click Add Custom Column.

9 - Reverse GeoLocation in Power BI

Note:  convert Latitude and Longitude values to text if you run into a data type error.

Now, expand the new column and select the fields and click OK.10 - Reverse GeoLocation in Power BI

You should see results here.

11 - Reverse GeoLocation in Power BI

You can also seen visuals on Power BI.

12 - Reverse GeoLocation in Power BI

I hope you found this blog post helpful. If you have any questions, please feel free to contact me muhammad.ahmad@kaispe.com.

Leave a Reply

avatar
  Subscribe  
Notify of