IP address mapping

[reno1]

I have a 'DirectQuery' table set up in Power BI with a field containing IP adresses. Ideally, I would like to visualize the locations of all the IPs using the Map. When I use the 'IP' field as a data input for the Map, I do get some IP addresses that show as a location. The issue is, that these IP addresses are mapped at the incorrect location. Most of the IPs don't even show on the map. I was wondering how others were able to map locations of IPs? I need a method that can automatically locate IPs with no user action, as new data is constantly being added to my table and I need the visualizations to keep in sync.

Any help would be greatly appreciated. 

[GilbertQ · Accepted solution]

Hi @reno1

You can do it with the following steps below.

You need to create a function with the following code below with the name of fn_GetIPAddress

let
    Source = (#"IP Address" as text) => let
        Source = Json.Document(Web.Contents("http://freegeoip.net/json/" & #"IP Address")),
        #"Converted to Table" = Record.ToTable(Source),
        #"Transposed Table" = Table.Transpose(#"Converted to Table"),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
    in
        #"Promoted Headers"
in
    Source

The function will return the output into a table.

Then what you do is where your data is in the Query Editor you click on Invoke Custom Function, then put in the required fields as shown below.

NOTE: Make sure to select your column above that has got the IP Addresses

Then once you click Ok you will see the table as shown below.

Click on the Expand Table button, leave all the defaults and click Ok.

You should then get the details for each IP Address.

NOTE: There are more columns but I snipped them off.

Please let me know if this works or you get stuck.

[GilbertQ]

Thanks @jnickell for the mention and yes your steps described will work!

[Anonymous]

Hey,

Waking up an old topic but a relevant for me now 🙂 Thanks for this already - the info on this thread has helped me quite a bit 🙂

However I have run into an issue. I have a large dataset of IP-address information updating every day (over 32k addresses) and I'd like to have the location info as well. This is working now ok, but seems silly to use the API gateway for addresses that I already have the geo info from previous runs.

Is it possible to store the previous info somehow and just use the gateway for new info?

I have currently two tables as I have optimized the gateway load by just getting the info based on address c-class.

table 1:

ip-addrees | reduced to c-class | bunch of stuff

8.8.8.8 | 8.8.8.1 | stuff

8.8.8.12 | 8.8.8.1 | stuff

9.9.9.9 | 9.9.9.1 | stuff

9.9.9.200| 9.9.9.1 | stuff

Table 2:

GeoClassToGet | the geo info tru the function

8.8.8.1 | the geo info

9.9.9.1 | the geo info

So the question is that can I just preserve the table 2 info on the geo info that has been gotten already thru the gateway? And just apped the new info somehow?

Thanks in advance! 🙂

[GilbertQ]

Hi there

Currently that would not be possible, but that could change when Incremental refresh comes to the Power BI Service.
You can currently do an incremental refresh in Power BI Premium. Where you could add a date for your IP Addresses and then only process the new ones.

[reno1]

@GilbertQ, thanks for the detailed answer! Really helpful. 

I cant seem to get it to work for me though. 

Firstly I go into the query editor, right click on the table containing the IPs, and select "Create Function....". Then I get this error message:

Then I go 'Create' > Enter the name of the function > Paste the code into the box > Continued with this

dialogue:

 

NOTE: My table is called 'LocationTest'.

I then go to where my data is in the query editor and try to invoke custom function. In this dialogue, I cannot select anything for 'Function Query':

Have I done something wrong here? I have a feeling that I'm not creating the custom function correctly. 

Thanks again for all your help.

[GilbertQ · Accepted solution]

Hi @reno1

The first step that you need to do, is to create a Blank Query.

Then go into the Advanced Editor for the Blank Query you just created.

Once in the Advanced Editor the copy and paste the code.

Once done you should see the function with the option to put in the value

[reno1]

@GilbertQ, managed to get it working great thanks to your help! Thanks heaps 🙂

[GilbertQ]

Hi @reno1

Awesome glad you got it up and running

[Anonymous]

Hi, 

I tried the genius method you posted (thanks btw). However, I get a bunch of errors on the rows that does not contain an IP address: 

Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=Type

Any suggestion on how to handle null values? 

[Anonymous]

Never mind, I figure it out myself. I had to replace the null value in the IP column with 0.0.0.0. Then it works 😄 

[dkay84_PowerBI]

I assume the subnet doesn't change the location of the ip range, correct?

Can you share the IP locations database? Or provide some sample data that we can play with?