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
6 years ago

Hi 

 

 

I'm getting the following error while trying to invoke a custom function

 

"Formula.Firewall: Query 'Final Table' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

 

Thank you

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
6 years ago
Look up “formula firewall error power bi”. This is thoroughly documented online.
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I am new to Power BI aswell, i really dont understand what you are saying.
    well, i have tried the way it is guided in this article. The code i am using for the function:

     

    (#"IP Address" as text) =>
    let
    Source = Json.Document(Web.Contents("https://ipapi.co/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"

     

    Tried below code aswell, same error:

     

    let
    Source = (#"IP Address" as text) => let
    Source = Json.Document(Web.Contents("https://ipapi.co/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

  • jnickell's avatar
    jnickell
    Helper V
    6 years ago

    Take a look at their API documenation on how to get a JSON result.  https://ipapi.co/#api

     

    You need to re-order how you are building the Web.Contents string.

    You're currently doing this:

    Source = Json.Document(Web.Contents("https://ipapi.co/json/" & #"IP Address")),

    You should try this:

    Source = Json.Document(Web.Contents("https://ipapi.co/" & #"IP Address" &"/json/")),

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    getting this error:

     

    DataSource.Error: Web.Contents failed to get contents from 'https://ipapi.co/json/*************' (404): Not Found

     

    Please help me 😞

  • jnickell's avatar
    jnickell
    Helper V
    6 years ago

    Well their web resource is still working (for me at least).  Maybe the way your Web.content is structured is incorrect? 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks alot sir. It worked, thanks once again.