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

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])
#"Promoted Headers"

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.

Sam Lester (MSFT)

Comments (8)
  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.

  3. Kirsty Moffatt says:

    Hi Sam,

    I seem to return just all null values. Do you think this could have something to do with the 2 IP address per second limit that the API specifies?


    1. Hi Kristy, it definitely could be related to the particular limitations of the API. I didn’t do any serious bulk processing since my list was somewhat small and so I didn’t research many other IP lookup services. If I had to process a significant amount of IP addresses, I would probably move this logic out to PowerShell where I could throttle the calls to keep within the limits of the API, then add the results to the report.

  4. Sultan Khan says:

    Hey Sam,

    Did it take quite a while to load? I’ve got around 8000 distinct IP addresses so would it be expected to take quite a while to load?
    Many thanks,

  5. Indhu says:

    When you publish to the web, how do you add a source to refresh for this type of custom invoke functions?

  6. Patrick says:

    I got this working perfectly thx for sharing.
    But I have the following problem: when I am in the query section the table gets updated. When I move back tot the report, again all data is refreshed which takes a very long time. Is it possible to change the function that the api is only called when for exemple the lattitude column is empty?

Comments are closed.

Skip to main content