IP to Countries
I had a request from a client, where he has an Excel sheet with list of IP addresses as well as other information, and he want to convert those IP address to the equivalent country and list in PowerBI.
Some Analytical tool like Splunk have this is a built in function, but for PowerBI you need to build it your self, and we will rely on a free web-service to implement this, so lets start
- Go to http://freegeoip.net/json/
- You should see the default data, which is your data retrieved by your IP address.
- Data displayed will be Country Code, Country Name, Region Code, Region Name, City, Zip Code(if applicable), Time Zone, Latitude, Longitude, and Metro Code
- if you entered specific IP address, correspondence data will be displayed, example: http://freegeoip.net/json/220.127.116.11
- Now, lets get back to PowerBI, Open your , Click Get Data, and then select Blank Query
- Click View, then Advanced Query, and add the Query in the right text
- Copy paste this Query and enter it in the text box of the Advanced Query
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")
- Click Done, and rename the function with a meaningful name like : fn_GetRegion
- Now, you need to create a column with this function, to do so, from Edit Queries go to Add Column > Invoke Custom Function
- Enter the data as shown below
- The column will appear at the end of your table, now we need to Expand the column by click on the double arrow icons as in the snapshot below
- Now, all the columns is appeared clearly.
- and you can use it to draw your maps.
Thanks to guavaq who shows us the solution in this post