Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.