IP Address Mapping in Power BI
I recently assisted in troubleshooting an issue where the error logs contained several unknown IP addresses. During this process, I created a quick dashboard in Power BI to display the location of these IP addresses on a map to get a better understanding of where the machines were located. I used a free service from IPInfoDB, which requires registration to obtain your API key, but is very straightforward and worked very well for this project.
The basis of this solution is in calling the free web service that returns JSON, then parsing this through M code to obtain the individual fields (country, latitude, longitude, etc.). Doing this manually through Get Data -> Web and passing the full URL, we see an example of the data returned by the lookup service.
Assuming that your Power BI report contains a column called “IP Address”, the following steps will allow you to create the map of IP address locations.
1. Create a new column that contains the full URL used to lookup each IP address.
= Table.AddColumn(#"Changed Type","FullIPURLCity", each "http://api.ipinfodb.com/v3/ip-city/?key=[URL_Key]&ip="&[IP Address]&"&format=json")
2. Replace the string [URL_Key] with the key obtained during registration (link above).
3. Create the lookup function in M (create a blank query, open Advanced Editor, paste the following code, and rename the function as GetAllFromIP).
Source = (FullURL) =>
Source = Json.Document(Web.Contents(FullURL)),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
4. Click "Close & Apply" to run the lookup function for each of the IP addresses in your report.
The sample .pbix file can be downloaded here.
Sam Lester (MSFT)