cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
reno1
Frequent Visitor

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 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. 

2 ACCEPTED SOLUTIONS

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.

 

Power BI - Invoke Custom Function.png

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.

Delete later - IP Address Table.png

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

You should then get the details for each IP Address.

Delete later - IP Address Table.png

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

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

30 REPLIES 30
GilbertQ
Super User
Super User

Hi @reno1,

 

From my understanding of IP Addresses you first need to do a lookup to an IP Address database or IP Address file in which you can then find out where the IP Address is allocated from. And then get the output from the IP Address database or file and use that to put it into a map.

 

I would assume you would need to get the IP Address database or file and then due to the structure of the file, you would need to do some process to find out where the IP Address sits within the range of allocated IP Addresses. Once that is done it would be able to link the location of the IP Address and use that in a map?

 

It would be a bit of a process to get this working.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

reno1
Frequent Visitor

@GilbertQ thanks for the response. 

 

I have found an IP address database and successfully imported it into power BI. What I am unsure about now is how to find where each IP address sits within the range of IP addresses in the IP database. The IP addresses I have are in the format '8.8.8.8', and the corresponding row in the IP database is '8.8.8.0/16'. Do you have any idea how to do this in Power BI, or where I can go to for information?

Hi @reno1, yes that would be the next challenge to convert it into the range of IP Addresses.

 

Another thought is if it is possible to pass the IP Address to a Online Lookup Service which would then return the location of the IP Address. And then store the value?

 

@dkay84_PowerBI, you are correct in that the Subnet does not affect the IP Address due to the IP Addresses being in ranges.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

reno1
Frequent Visitor

@dkay84_PowerBI, I got the database from 'http://dev.maxmind.com/geoip/geoip2/geolite2/'. I downloaded the csv files and added them to my SQL Server database. I then imported the tables into Power BI.

 

@GilbertQ, I also had this thought before posting the question. I was planning to use the site 'http://freegeoip.net/' to get the location. For example, when I go to the URL:

http://freegeoip.net/json/8.8.8.8

it returns the JSON:

{"ip":"8.8.8.8","country_code":"US","country_name":"United States","region_code":"CA","region_name":"California","city":"Mountain View","zip_code":"94035","time_zone":"America/Los_Angeles","latitude":37.386,"longitude":-122.0838,"metro_code":807}

This provides all the information I need, though I cannot figure out how to do this automatically for each IP address in Power BI, leading me to ask this question.

 

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.

 

Power BI - Invoke Custom Function.png

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.

Delete later - IP Address Table.png

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

You should then get the details for each IP Address.

Delete later - IP Address Table.png

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

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

Hello,

I make all steps but I have a problem :

Flootifloo_0-1632989524479.png

 


"DataSource.Error: The downloaded data is of type HTML, which is not the expected type. The URL may be wrong, or you may not have provided the correct credentials to the server.".

How could I fix it please ?

Thx

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

Look up “formula firewall error power bi”. This is thoroughly documented online.

getting this error:

 

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

 

Please help me 😞

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

 

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

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/")),

 

Thanks alot sir. It worked, thanks once again.

This answer from guavaq  is totally awesome! Amazing! Thanks!

 

🙂

Uh... is this legal!? But seriously, good solution, thanks!

 

Also side-question: how do you reconfigure security/permissions for this function (when connected to DB as your source)?

 

Power BI asked me to designate security levels(Public, Organizational, Private) between the database i'm connected to and this freegeoip.net.

 

I marked my database as organizational and freegeoip.net as public which returned a Firewall error.

 

OR should I ask my admin? 

Hi there, I do not think it is an issue, might need to read the details on their website?

Yes that would be because you are mixing data sources, which then asks about privacy settings.

What I would suggest doing is unless you have very strict security policies in place change the Security Level to Off, so that the two datasets can work together. It is controlled within Power BI Desktop only.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

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? 

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

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

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.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.