How to Extend Your Data with Live API Calls: Adding Location from IP Address

by karaoan on ‎12-02-2018 10:36 PM

Power BI offers fantastic ways to integrate data from online sources. Often it is useful to just use parts of that and extend your own data set with details from an online API. Here -as promised in our previous post https://www.managility.co/how-to-automate-collection-of-power-bi-activity-data/ - we are covering how you can add geographic details for an IP address that could be part of your usage logs.

For this example we are using the IP location module from IP Stack.

1. Create an IP Stack user account and generate the API Key

The online service "IP Stack" offers an API to geolocate IP addresses, thankfully there is a free plan account, which gives us 10,000 calls to the API per month.

Let’s register an account and get the free API key.

Once account is registered, and activated, we can login and get the API Key for our purpose.

Now, we have the API access key, copy the API key to your notepad, you will use it later on.

2. Get Geolocation information from Power BI

First, let’s create a table in Power BI with an IP Address column, which we will be using it for getting our geolocation information, of course, you can also use that with an existing data set that contains an IP Address and use that column to retrieve the geolocation later on.

Let’s fill in some random IP Addresses, and name the table as “Location”.

Click “Load” and the table will appear in the Power BI Desktop. Click the “Data” view at the left hand side of the window. And Right Click “Edit Query” on the table.

Click “Add Column” on the menu bar, and select “Custom Column”, the custom columns will be created to call the IP Stack API and retrieve the geolocation based on the IP address column.

Use the following formula to create your custom column by calling the IP Stack API using your own API Key.

Json.Document(Web.Contents("http://api.ipstack.com/"&Text.From([<YourIPAddressColumn>])&"?access_key=<Your IP Stack API Key>"))

Click “Ok”, then you will see that the Custom column was created as a Record Type.

Click the “expand column” icon, and you will see a list of columns returning back from the API calls, you can select multiple columns like country city to suit your purpose, in my case, I will select all.

Navigate to the menu “File” and Select “Close and Apply”

Now, let’s see and wait for the apply query changes.

Once loaded, we can see all other columns are generated and now we can do further analysis in Power BI report.

As usual you can contact us on: https://www.managility.co/contact/ for any further questions.