Power Query Custom Functions

If you're familiar with using Excel functions (and of course you should be) then you'll know that a function typically takes some input and gives you an output.

The input, for example, could be a range, a number or a piece of text. The output type will vary, the SUM function will output a number, LEFT will output a string.

A custom function in Power Query works in a similar way, an input gives an output, but a custom function allows us to make a calculation for every row in a column, or columns, and create a new column from those results.

A custom function is just a type of query containing steps like any other query, and can be written to do complicated things like scrape data from web pages or retrieve data from a database.

Basically it can be any series of steps that you may want to repeat for a given set of input data.

Let's look at some simple examples.

Custom Function to Square a Number

Let's say we have a column of numbers in a table, and we want to calculate the squares of these numbers.

Click into the table and then create a new query: on the Ribbon -> Data -> From Table/Range.

The table is loaded into Power Query

Create a new blank query: right click in the Queries area -> New Query -> Other Sources -> Blank Query

Right click on the new query and then on Advanced Editor.

To create our function we need to specify an input parameter and write the steps to calculate our output.

Our input is specified by the line (TheNumber as number) => which means this query will take an input that will be referred to as TheNumber and it will be of type number.

To calculate the square of TheNumber we just multiply it by itself.

Click done and you'll see on screen that you can enter a parameter (input) to test the function. Enter any number and and click Invoke.

We don't need this invoked function query so right click on it and delete it.

Next, right click on the function named Query 1 and rename it to fxSquared

Or click on it and rename it in the Name box on the right hand side of the screen under Properties.

There are a couple of ways we can use our new function. By clicking on the Add Column section of the Ribbon we can then use either Custom Column, or Invoke Custom Function.

Using a Custom Function in a Custom Column

With the Numbers query selected, click on Add Column and then Add Custom Column, and you'll get the Custom Column dialog box appearing.

In the Custom Column Formula area start typing fx and Intellisense should pop up showing our custom function. Press TAB or click on the function name to enter it into the formula.

Now we need to supply the function with an input which will be the numbers from the number column. Type ( and then you can double click on Number in the Available columns list. If our table had more than 1 column they'd all be listed here to choose from.

Now close your parentheses ), click OK to create the custom column

and your screen should look like this with a new column of squared numbers.

Close and load the query to your worksheet.

Add a Column by Invoking a Custom Function

You can achieve the same result by Invoking a Custom Function : from the Ribbon -> Add Column -> Invoke Custom Function

Give the new column a name : Squared

Choose the Function query : fxSquared

Choose the input column for the TheNumber parameter : Number

Click OK and you will have a new column called Squared containing the squares of the numbers in the Number column.

Custom Function for Currency Conversion

I have a table of amounts in Australian dollars and I want to convert these to US dollars.

Click in the table then create a new query from the Ribbon -> Data - From Table/Range

As before, to create a function, Right click in the Queries area -> New Query -> Other Sources -> Blank Query.

Right click on the new query and click on Advanced Editor.

The following image shows the changes to make to create the currency conversion function. It takes one parameter called Amount and uses a value called Rate which is set at 0.73, this is the AUD/USD conversion rate right now.

The function returns the value Amount * Rate

Rename the function to fxConvertCurrency

Add a Column by Invoking a Custom Function

Follow the same steps as before to Invoke a Custom Function

We end up with another column holding the USD values for the AUD amounts in each row.

That's great but by hard coding the exchange rate into the function, it makes it difficult to maintain and it's just bad practice.

It would be better to get the exchange rates dynamically and not hard code any values into the function.

If you have Office365 you could get up to date currency exchange rates by using the STOCKS data type in Excel and reading those values into your query.

Or you could write a query/function to get the rates from an API, or by using VBA or Power Query to scrape a website that provides exchange rates.

I won't go into that process now, let's just say we've got a new table called Currency_Rates with the most up to date exchange rates, like so.

After clicking into the Currency_Rates table, create another query From Table/Range.

Now in the real world you'd probably just modify the fxConvertCurrency function at this point. But for the sake of demonstrating, let's duplicate that function instead and we can modify the duplicate.

Right click on fxConvertCurrency and click on Duplicate. Rename the duplicate to fxConvertCurrencies.

Right click on the new fxConvertCurrencies function and then open the Advanced Editor.

All we need to do is change the code so that the function takes two input parameters, so add Rate as number as the 2nd parameter. Don't forget the comma between the two parameters.

As Rate is now supplied as a parameter, we don't need to declare it in the function so delete the whole line Rate = 0.73, and you should end up with this. Click Done.

Click on the Currency_Rates query and then Add Column -> Invoke Custom Function.

Give the new column a name : USD Amount

Choose the custom function to use: fxConvertCurrencies

Select the columns to use an inputs, in the same order as the input parameters are listed Amount then Rate : Amount and USD Rate

Click OK and you'll have a new column with the various currency amounts converted to USD with the correct conversion rate.

Summary

I've covered some straightforward examples of custom functions, but they can be used for very complicated and useful things.

In subsequent posts I'll be looking at using them to create things like running totals or calculating the percentage of parts compared to a whole, for example, if a customer makes multiple purchases, what is the $ value of each purchase as a percentage compared to the total amount spent?