mass ip geolocation

[Anonymous]

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 have around 3900 distinct Ip addresses altogether. I then need to hook this up to my masterfile which contains various details about users etc. 

Ideally I'd like to use the maxmind API to get "Country", "City", "longitude", "latitude" and "is_proxy" data. I know of a previous solution posted by @GilbertQ however since then, that site has since been deprecated. The data from that also doesn't suit my own purposes. 

I know this is doable, but just lack the knowledge in M to do this! 

https://www.maxmind.com/en/geoip2-precision-services 

Many thanks

[GilbertQ]

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

[indhu]

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]

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]

@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

[GilbertQ]

Hi there

As per there documentation you will have to sign up and get an API Access key.

Then when you query as part of the query string you would need to put in the API Key in order for it to work

[indhu]

@GilbertQ did that and in that M code replaced <API> with the actual API key. Got the following 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

[GilbertQ]

Hi there

Did you test it manually via the URL?

If that works make sure that there are no spaces when passing it through via the function

[indhu]

Worked. Thanks a lot 😄

[GilbertQ]

Awesome glad you got it working!

[Anonymous]

Back again, Gilbert! So we opted to continue with the IP stack API. I seeimingly still cannot get the function work once I click close & apply. The code definitely works as it loads up the table I want once I have invoked the custom function. The issue I keep coming up against is a web access issue after. I am using the http version of the website not the https. Is there a way of running the function so I can avoid this? 

This is my code below.

= (#"IpAddress" as text) => let
Source = Json.Document(Web.Contents("http://api.ipstack.com/"&#"IpAddress"&"?access_key=MYAPIKEY"& "&security=1")),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
in
#"Promoted Headers"

Thanks, 

S

[GilbertQ]

Hi there.

I think you need to make it. Anonymous and put the API key as part of the url

[Anonymous]

How would I go about doing that? I'm only provided with two options. The website or the website + the IP address without the access key to follow. 

 

[Anonymous]

Will try this method hopefully it works and if it does, I owe you a virtual glass of champagne.

[GilbertQ]

Please let me know if it works.

Looking forward to that virtual glass of champagne

[Anonymous]

= 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" 

[GilbertQ]

Hi there,

If you can change the double quotes to be ", as due to the copy and paste they are the italic double quotes

= Table.AddColumn(#"Changed Type1", "ParkRun", each Function.InvokeAfter(()=>fx_ParkRunData([Run]), #duration(0,0,0,#"Refresh Interval")))

[Anonymous]

Hey @GilbertQ, No worries I got it working. Takes a pretty long time but thats expected I have 4.3k Ip Addresses. Not sure if my issue is with the API service or not now because there is a limit. But I will come up with an issue that says "access to the resource is forbidden" with the url of the website "http://api.ipstack.com/[IpAddress]". This is the same issue I faced previously when using the API and seems the InvokeAfter.Function won't do the job.... 

Any other ideas? 

[GilbertQ]

Hi there

I think it might then be a limit on the site in terms of how many free IP Addresses you can geocode?

[Anonymous]

So I’ve spoken to them and they said that it’s an issue with Power BI itself. The Lead analyst at ups tack said there wasn’t a request limit on my service. Any ideas from tech?

[GilbertQ]

Hi there,

I have done Geocoding over a larger dataset without any issues.

I would ask them to explain what is the error and why it is happening. It appears to me that they are just saying it is a Power BI issue without proving anything.