In the past I did create a blog post where I demonstrated how to get the location of a user based on an IP Address

What I did forget to elaborate on and what really makes Power BI even more powerful is how easy it is to create and use a function. So this is the goal of my blog post today.

What is a Function and how can I use it?

I am going to try and explain what a Function is in the easiest way possible, which I hope provides you with a better understanding.

A function has a starting point or input which you then can re-use to do the same thing over and over again.

For example let’s say your starting point was an address where you went to deliver a newspaper. Now every time you delivered the newspaper you needed to make a note if there was a post box or not. So in order to do this you would need to get the address, go to the location and once there find out if there was a post box or not. Once the first address was done, you would then go to the second address, find out if there was a post box or not and continue with this.

With a function you go to one address to find out if it has a post box or not. Then what you do is save the steps on how you found out if there was a post box or not. Now that you know how to find out if there is a post box or not, you can reuse these steps by putting in a different address at the start of your steps.

Working Example

In this example below I am going to show how I created my table to get the location of a user based on their IP Address.

And then converted this into a Function. After which how I then used this function in my dataset.

Getting my location of a user based on an IP Address into a table

The first step is I must first create a Parameter, which will be what I am looking to use each time. As with my example it will be the IP Address.

NOTE: The reason for creating the Parameter now, is because it is a requirement when creating a function, as it needs something to start with in order for the function to do something. It is like saying that you want to go somewhere, but without an address you cannot go there.

  • I open Power BI Desktop and go into the Query Editor.
  • I then click on Manage Parameters and click on New.
  • I then put in the following details for my Parameter as shown below.
    • Then click Ok.

The second step I did when creating my function is that I actually go about getting my data as I would with any data source.

  • Still in the Query Editor, I clicked on Get Data and selected Web
  • I then clicked on Advanced from the From Web Window
    • NOTE: The reason for this is I want to pass through my IP Address Parameter as part of the URL, which I will demonstrate below.
    • Under URL Parts I put in the website where I wanted to get the location from the IP Address which with my example was:
    • Then in the row below I clicked on the Drop down and changed it from Text to Parameter
    • And then I selected my Parameter which I created above called IP Address.
      • I then clicked Ok.
    • This then loaded the data.
  • As you can see this then loaded it into a list due to it returning a JSON document.
  • In the ribbon I then clicked on Into Table to convert it into a table.
  • Once that was done I then created a few steps so that I could get my data into a format that I wanted so that I could know what the IP address is.

Now this is where it starts to get fun, because now what I have done is manually created the steps to get the IP Address details.

And this is now where as explained before a function comes into its own, because what it can do is it can repeat all these steps for me for each IP Address, so I do not have to go and do it for each and every IP Address. So in the steps below I am going to now show you how to create the function.

Creating the IP Address Function

In the steps below I am now going to show you how I create the IP Address function.

  • Still in the Query Editor I right click and select Create Function
  • Now the Window appears and it asked me to give the function a name.
  • What I always do when creating functions is prefix it with an “fx” so that I know it is a function.
    • As you can also see below, because I created the IP Address parameter earlier it is showing you that it is going to use the IP Address Parameter.
    • Then I clicked Ok.
  • Now once this is done it then puts it into a folder with the parameter, original table and the function.
    • Now the thing to understand here is that because it puts it all into a Group if you change the Parameter or anything in the table it will automatically make changes to the function.
    • How cool is THAT!

Using the fx_GetIPAddress function to get my IP Address details

Now the final piece is where I am going to show how to use the function to all the hard work and go through each IP address and find the IP Address location for each IP Address.

  • I created this simple table in Excel below, so replicate a dataset, and in reality I could have a whole lot of columns, but all that I am interested in is the IP Address
  • So once I have imported it into Power BI it looks like this.
  • Now this is where I just smile because all the hard work has been done creating the initial parameter, table and then converting it into a function.
  • Now to use the function in the ribbon I go to Add Column and then click on Invoke Custom Function
  • This then opens the Invoke Custom Function Window and from the drop down I select my function I created earlier fx_GetIPAddress
    • NOTE: You must make sure that for the IP Address, that you select the right column from your data.
      • As with my example I had to select the IP Address column.
    • And then click Ok
  • Now once this has completed you will see that it has got a new column called fx_GetIPAddress and it says Table in Yellow, meaning that this can be expanded.
    • A really neat trick is that if you click on the actual Table column as shown below it will show you the contents of the table. So it is like a sneak peak, so I can make sure that it has the data that I want.
  • The thing to realize now is that what it has done is it has taken each IP Address, gone through all the steps earlier that I did to create the single table, but it has applied it to each and every IP Address.
  • Once again HOW COOL IS THAT, it just did all the hard work for me. If I had 100 IP Addresses it would have repeated it 100 X.
  • So now I can click on the Expand Button and expand the Table so that I can get the additional columns.
    • NOTE: I did remove the tick from “Us
    • e original column name as prefix” so that it would not prefix all of the columns with the function name.
  • So now my dataset looks like the following below.
  • I then clicked on Load and Apply to load it into my Power BI Dataset.
  • Then what I then did was to change the data categorization for my Latitude and Longitude to the correct values.
  • And finally I then plotted it on a map
  • Yes this is not the best dataset, but I wanted to show how to using functions can make the repeatable steps a lot easier.

Conclusion

What I showed in this blog post first I explained what a function is. Then I showed how to create the parameter, table and create the function. Then I showed how to use the function in the dataset so that it would do all the repeatable tasks.

If there are any questions please leave them in the comments below.

And finally you can get a copy of the file here: Function – Getting IP Address Details.pbix