3

Currently trying to add a map of users, by IP, from an SQL-Server data source. The IP's either map to a wrong location, or don't map at all using Power BI's inbuilt lookup for them.

Is there a way to query location via the IP column? Or any known API to do this for me?

Mattcul
  • 242
  • 1
  • 8

2 Answers2

2

Try with this: freegeoip.net

http://freegeoip.net/xml/8.8.8.8

It returns:

<Response>
<IP>8.8.8.8</IP>
<CountryCode>US</CountryCode>
<CountryName>United States</CountryName>
<RegionCode>CA</RegionCode>
<RegionName>California</RegionName>
<City>Mountain View</City>
<ZipCode>94035</ZipCode>
<TimeZone>America/Los_Angeles</TimeZone>
<Latitude>37.386</Latitude>
<Longitude>-122.0838</Longitude>
<MetroCode>807</MetroCode>
</Response>

Or using JSON:

http://freegeoip.net/json/8.8.8.8

{"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}
McNets
  • 10,352
  • 3
  • 32
  • 61
  • How would I go about incorporating this IN to power bi? – Mattcul Mar 08 '17 at 03:40
  • 2
    Open a data source, Use Connect to data -> Web and use this url. You'll get a table with this values. I've checked it, and returns a table with one column by field. https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-common-query-tasks/ – McNets Mar 08 '17 at 14:04
  • I'm glad to be able to help. – McNets Mar 08 '17 at 21:32
2

I faced a similar problem, and here's what I did.

  1. I created a New table with distinct IP addresses from the source data table.
  2. Created a custom function which Queries the IP address and get the details for it (see at the bottom).
  3. Clicked Add Column in the Ribbon and clicked on Invoke Custom Function
  4. Once the query was completed, Clicked on the New Column and Expanded the table by clicking Icon on Top Right of the column.

-- The Function Code that worked for me is

= 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

Source

Gagan
  • 49
  • 6