Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

mass ip geolocation

Hey PowerBI,    I'd like to conduct a mass look up of all IP addresses within my file providing them with associated latitudes and longitudes so I can accurately locate where they map out to.  I ha...
Need Help
GilbertQ's avatar
GilbertQ
Super User
8 years ago
Hi there

I have a blog post coming out later today (Australian time) where I explain how to use a different IP Address Lookup with a way to limit how quickly it will query the API.

You can find it here: https://www.fourmoo.com/blog
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    Will try this method hopefully it works and if it does, I owe you a virtual glass of champagne.
    • GilbertQ's avatar
      GilbertQ
      Super User
      8 years ago
      Please let me know if it works.

      Looking forward to that virtual glass of champagne
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        = Table.AddColumn(#”Changed Type”, “fn_GetIPAddressDetails”, each Function.InvokeAfter(()=>fn_GetIPAddressDetails([IpAddress]), #duration(0,0,0,#”Interval (Secs)”))) 

         

        So i paste in the above code as your step says over the Invoked function column. But I get the error "Invalid Literal" 

  • indhu's avatar
    indhu
    Helper III
    8 years ago

    Hi GilbertQ, 

     

    Thanks for the wonderful post. The problem I am facing when trying to implement is, access restriction when trying to use freegioip.net. When I further researched I found that their address has been changed to ipstack.com 

     

    How should we design for this particular website? they changed the plan and not sure how to do that.

     

    Thanks,

    Indhu

    • GilbertQ's avatar
      GilbertQ
      Super User
      8 years ago

      Hi indhu

       

      It appears that they have got a free option which has got 10 000 lookups.

       

      Otherwise you would have to pay for a lookup service?

      • indhu's avatar
        indhu
        Helper III
        8 years ago

        GilbertQ 10,000 looks up is fine. I replaced your following code to this,

         

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

        Replacing the source name to new web address. I am getting this error,

         

        DataSource.Error: Web.Contents failed to get contents from 'https://ipstack.com/json/203.56.182.115' (404): Not Found
Details:
    DataSourceKind=Web
    DataSourcePath=https://ipstack.com/json/203.56.182.115
    Url=https://ipstack.com/json/203.56.182.115

        Can you please point me out what I am missing?

         

        I also tried another site, https://www.ipinfodb.com/api Following your post and replaced the M query as follows,

         

        let
Source = (#"IP Address" as text) => let
Source = Json.Document(Web.Contents("http://api.ipinfodb.com/v3/ip-city/?key=<APIkey>" & #"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

        And getting this error,

         

        An error occurred in the ‘fn_GetIPAddressDetails’ query. DataFormat.Error: We found extra characters at the end of JSON input.
Details:
    Value=
    Position=0

         I badly need this to work :( Please help

         

        Thanks, 

        indhu