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 http://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 the account is registered, and activated, we can login and get the API Key for our purpose.
Now, we have the API access key, copy it to your notepad, we 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 as the basis to derive geolocation information. Of course, you can also use an existing data set that contains an IP address and use the respective column from there.
Enter a few random IP addresses, and name the table “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 function to create your custom column to call 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 the API call results, you can select multiple columns like country city as required, in my case, I will select all.
Navigate to the menu “File” and Select “Close and Apply”
Now, let’s see and apply the query changes.
Once loaded, we can see the new columns were generated and now we can do further analysis in a Power BI report.
As usual you can contact us on: http://managility.co/contact/ for any further questions.