IP Address Mapping in Power BI


Sam Lester Power BI Blog

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.

IP Address Lookup

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).

Power BI Sam Lester

3. Create the lookup function in M (create a blank query, open Advanced Editor, paste the following code, and rename the function as GetAllFromIP).

let
Source = (FullURL) =>
let
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])
in
#"Promoted Headers"
in
Source

4. Click "Close & Apply" to run the lookup function for each of the IP addresses in your report.

IP Address Mapping in Power BI

The sample .pbix file can be downloaded here.

Thanks,
Sam Lester (MSFT)

Comments (3)

  1. Bill Grant says:

    Link to PBIX is not lit…

    1. Thanks Bill, I just updated the link after posting it to the Power BI Data Stories Gallery.

  2. RUPESH SHARMA says:

    This is very useful. I need similar dashboard for the bus routes on map. Bus stops location’s coordinates not available and will have to be looked up manually.

Skip to main content