Forum Discussion
IP address mapping
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 SourceThe 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.
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
SourceThe 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, thanks for the detailed answer! Really helpful.
I cant seem to get it to work for me though.
Firstly I go into the query editor, right click on the table containing the IPs, and select "Create Function....". Then I get this error message:
Then I go 'Create' > Enter the name of the function > Paste the code into the box > Continued with this
dialogue:NOTE: My table is called 'LocationTest'.
I then go to where my data is in the query editor and try to invoke custom function. In this dialogue, I cannot select anything for 'Function Query':
Have I done something wrong here? I have a feeling that I'm not creating the custom function correctly.
Thanks again for all your help.
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
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.
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
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 😞
Hello,
I make all steps but I have a problem :
"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
