Forum Discussion

reno1's avatar
reno1
Frequent Visitor
9 years ago
Solved

IP address mapping

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...
Need Help
Tips and Tricks
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    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's avatar
    GilbertQ
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hey GilbertQ I'm having issues logging into the database because my access is restricted is there a way around this? Or is this purely down to my organizations firewall? 

GilbertQ's avatar
GilbertQ
Super User
8 years ago
Hi there

If you cannot log in, then I would assume it would be from where your On-Premise Gateway server is installed to your Database.
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Sorry, 

     

    I'm a little bit of a tech newbie, could you explain?  It says "Access to the resource is forbidden" but having spoken to my IT department thats because the website has now changed to api.stack.com. They have a new method but being so new to M not sure how to incorporate my access key as well the IP address into the code you wrote above? Could you please advise?  

     

    https://ipstack.com/documentation

  • jnickell's avatar
    jnickell
    Helper V
    7 years ago

    I stumbled on this thread because of a limitation I was seeing in the ESRI ARC GIS map (unable to control zoom) as the dataset changed.

     


    I was able to follow GilbertQ's directions and get things setup and functioning.  As you mentioned they method of access has changed a bit.  

     

    You'll need to setup a Parameter First (IP address)

    Add a Web Data Source

    Switch to Advanced Mode so you can specify multiple parts

    For the first part supply their website address upto and including the "/"

    For the second part select to use your parameter

    For the third part specify ?access_key=.... for you access key

     

    From there you can convert it to a function and should be able to follow his guide.

     

  • GilbertQ's avatar
    GilbertQ
    Super User
    7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
    GilbertQ
    Super User
    7 years ago
    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.