I recently had a question from a user in the Power BI Community page who wanted to know where the people were coming from based on their IP Address. The IP addresses were stored as part of the dataset, but to try and go and do this with the IP Address database meant that you would then need to go and translate the IP addresses into a number, to cross reference across the IP Address ranges.

My solution below rather uses the web lookup, which will work using any dataset, as well as simple and easy to use.

Adding the Function into your Query Editor

  • The first thing that you will need to do is to create the function which I did with the following steps below.
    • Click on New Source, and then select Blank Query
    • Next rename it from Query1 to fn_GetIPAddressDetails
      • You can do this by right clicking and select Rename
    • Next in the Home Ribbon under the Query section click on Advanced Editor
    • Now paste in the following Power Query (M) syntax
      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
    • Then click Ok.
    • Now you should see the following for your function
  • What the above does is it takes the input of IP Address
  • Then what it does it takes the IP Address and then using the above service http://freegeoip.net looks up the IP Address and returns the details.
  • Next it converts the JSON to a table.
  • After which I then transposed the data.
  • And finally promoted the first Row as Headers

Using the Function with your Data to output the IP Address details

In this step I will now show you how to use this function to get the output from your IP Addresses, in your data.

  • I have used a sample file in which I made up the IP Addresses as shown below.
  • I then went into the Add Column in the Ribbon and clicked on Invoke Custom Function
  • This brings up the Invoke Custom Function window and I put in the following information as shown below.
    • As you can see from above, I gave my new column a name of Details
    • I then clicked the drop down and selected my function I created earlier called fn_GetIPAddressDetails
    • And then finally the crucial part is where I selected my IP Address Column.
    • I then clicked Ok.
  • When you do this it returns a table as shown below.
  • Click on the Expand Table Button on the top right hand side, which will then prompt you which columns you want to select
  • I left them all selected and clicked Ok.
  • And as you can see below, here is the first 3 columns from the list
  • I then loaded my data into my Power BI Model and created a map visual using ESRI

So in conclusion you can see it is very easy to use Power BI to create functions, which can iterate over a your dataset and give you a meaningful output with not a lot of effort, where in the past this used to take a significant amount of effort.

You can download the sample file here: Get IP Address Details.pbix