This post may contain affiliate links. Please read my disclosure for more info.

Power Query – Custom Functions

Power Query - Custom Functions

In this post, we’are going to explore the topic of custom functions; What are they? When should we use them? And how to create them?  At the time of writing, there are over 600 standard functions already in Power Query.  Yet, those functions won’t always do what we need, or in the most efficient way, therefore we might need to turn to custom functions to achieve the desired end result.

What are custom functions?

Functions are processes or procedures which consistently perform a task. For example. Excel’s SUM function will consistently add up values, or VLOOKUP will consistently return an equivalent matching value from a list.

However, just because functions in Excel normally calculate numeric and text results, this doesn’t mean this has to be the case.  Actually, this is quite a limiting belief. A function can be used to perform a variety of tasks, and this is especially true with Power Query.  We can use a custom function to get data from a web page, or to read the data in a file.  From a Power Query perspective, it is better to think of functions as any routine task that can be applied over and over again.

I thought for quite a long time about whether to include custom functions in this series as they can be pretty tricky.  But I decided that were to value to exclude.  This post will involve a bit of coding and changing the automatically generated M code, so follow along carefully.

We’re going to tackle this area by working through two examples (1) an easy custom function (2) transformation based custom function.

Downloads

To work along with the examples below, download the sample files.  Click here to subscribe and get access to the Downloads section.

Excel Downloads

We will use the same file as a previous post.  The examples below use the file called Example 15 – If Function.xlsx

The example file contains just one Table.  To load the data into Power Query, select any cell in that Table and click Data -> From Table/Range.

Data - From Table or Range

Give the query a useful name – I’ve called it Data, and this is how I’ll refer to this query later in the post.

Easy Custom Function

In this first example, we are going to create a very easy custom function.  It is purely to illustrate the process so that when we get into the latter example, it won’t be an overwhelming learning curve.

Create a blank query by clicking Home -> New Source -> Other Sources -> Blank Query

Create Blank Query

Give the query a useful name, for illustration purposes I’ve called it myFunction1 (don’t use that name in the real world, select something more descriptive, but it will work for our illustration).

Open up the Advanced Editor for that query by clicking View -> Advanced Editor (or Home -> Advanced Editor).

View - Advanced Editor

The Advanced Editor window will look like this:

Advanced Editor Window - Blank Query

Even though it is a Blank Query, we are still provided with some initial code.  The let and in statements will remain in the final code, but we will change the rest of it.

In our first example, our goal is to create a custom function that will multiply the value by 10.

Enter the following text into the Advanced Editor.

REMEMBER: M code is case sensitive, so type carefully 🙂

(Value) =>
let
    nameOfStep = Value * 10
in
    nameOfStep

We will deconstruct this code a bit later in the post, but for now, just click Done to close the Advanced Editor window.

We will now see the following.  This is where we can test the function we have created.  Enter a number into the Value box and click Invoke.

Invoke the basic function

If we have done everything correctly, it should calculate the correct value and display it back to us.

Result of invoked function

By invoking the function Power Query will have created a query called Invoke Function, which we don’t need this, so it can be deleted.  In the same query list, you will have the query we created at the start (which we called Data) and the function we created (called myFunction1).

Next, open up the Data query we created at the start.  Create a Custom Column (Add Column -> Custom Column) and use the custom function we created.

The code to use in the custom function window is:

=myFunction1([Value])

Note: The word [Value} in the function is the name of the column to be used as the argument.

Using Basic Function in the table

Using a custom function is just like using a standard Power Query function.

Deconstructing the M code

Let’s deconstruct the code we created earlier to understand what it does.

Declare the arguments

The first line declares the arguments (or inputs) to be used in the Function.  In our example, it only has one input, which I have called Value.

(Value) =>

The second line identifies the start of the transformation steps; there is no need to change anything in this line.

let

Next, we created a step, which is called nameOfStep.  There are some rules about naming steps, especially in regard to using spaces.  However, rather than getting into that now, it’s easier to always exclude spaces when creating steps manually.

nameOfStep will take the Value (the argument created in the first line) and multiply it by 10.

nameOfStep = Value * 10

Finally, we return the result of the function.  in denotes the end of the transformations.  After the in statement, we declare the name of the step to be returned.  In our case, we only have one step, so we will return nameOfStep.

in
    nameOfStep

Multiple arguments and types

When using standard Power Query functions, you will notice they can have multiple arguments and require specific data types.  We can do the same in our own custom functions, which is all handled in the argument declaration at the start of the query:

Declaring the input type

To declare a text data type, we change the code as follows:

(myText as text) =>

To only permit a table as the argument we would use:

(myTable as table) =>

Pretty easy, right.  As you know, there are lots of data types.  If you’re unsure of the code for a data type, then use another query, make a data type change through the normal user interface, then review the code created to identify the change.

Declaring the output type

Power Query also has a data type for the output of the function; this is stated outside the brackets.

(myText as text) as table =>

The example above shows the function takes a text value as an argument, and the result returned is a table data type.

Declaring multiple arguments

To declare multiple arguments for the function we list them inside the brackets.

(myText as text, myTable as table) as number =>

The declaration above has:

  • Two arguments called (1) myText (with a text data type) and (2) myTable (with a table data type).
  • Output has been declared as a number data type

Custom functions containing transformations

When we imported data from the web in a previous post, we used this same methodology.  Go check out that post for a bit more practice.  However, this time, we’re going for something a little trickier.

For this example, we are going to calculate the % which is transaction is of the total purchases for that customer.  So, if ABC Discounts has purchased $122 what percentage of that total was each transaction.  The screenshot below is the final result we are trying to calculate.  ABC has two transactions, the first being 37.7% and the second being 62.3%.

Customer Percent by Product

In Excel terms, this transformation is like dividing the value in each row by the SUMIFS of the Customer.

Duplicate the query

Duplicate the Data query we imported at the start.  This is because we need (1) a query as a function (2) a query in which to use the function.  To do this, right-click on the query in the query list and click Duplicate.

Duplicate Query

Rename the query

As we saw above, the name we give to the query is used to call the function.

I’ve given the query the name fnCustomerTotal.  Placing fn at the start has become a common practice for custom functions; it helps to visually identify that it is a function.

Transformations

Delete all the steps, except the Source step.

Next, click Transform -> Group By.  We saw how to use the Group By functionality in the previous part of this series.  In the Group By window, enter the following settings:

  • Column: Customer
  • New column name: Customer Total
  • Operation: Sum
  • Column: Value

Group By Window

Click OK

Filter on the Customer column to select a single customer (ABC Discounts in this example).

Filter on Customer Name

Change the query into a function

Open the advanced editor:

Advanced Editor - M Code

Here comes the tricky part… making the correct changes to the M code.  eeek!!!

Declare the arguments as the first line in the code.  We only need two arguments (1) the table to use and (2) the customer name to filter on.

(sourceTable as table, customerName as text) =>

As we will be using a declared table as the source, we don’t need the Source step which already exists in the code.  Delete the following line.

Source = Excel.CurrentWorkbook(){[Name="tblJanuary"]}[Content],

In the next line, there is still a reference to the Source step which we have just deleted.  We need to point that line to our declared argument.

Change this:

#"Grouped Rows" = Table.Group(Source, {"Customer"}, {{"Customer Total", each List.Sum([Value]), type number}}),

To become this:

#"Grouped Rows" = Table.Group(sourceTable, {"Customer"}, {{"Customer Total", each List.Sum([Value]), type number}}),

Now, change the hard-coded name of the Customer to the declared customerName argument:  Change this:

 #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Customer] = "ABC Discounts"))

into this:

 #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Customer] = customerName))

We could use this in its current form.   If we were to, it would return a Table containing a single value, rather than a single value itself.  But we can go the extra step to just return the value.

Make the following changes to the M code. Change this:

    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Customer] = customerName))
in
    #"Removed Columns"

into this:

    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Customer] = customerName)),
    CustomerTotal = Record.Field(#"Filtered Rows"{0},"Customer Total")
in
    CustomerTotal

Record.Field is a Power Query function which returns single value from a column:

  • #”Filtered Rows”{0} represents the first row from the table as it existed after the Filtered Rows step
  • “Customer Total” is the name of the column

Warning: did you notice the additional comma at the end of the #”Removed Columns” line?  It needs to be there, so go add it in, if you’ve missed it.

The complete M code should be like this

(sourceTable as table, customerName as text) =>
let
    #"Grouped Rows" = Table.Group(sourceTable, {"Customer"}, {{"Customer Total", each List.Sum([Value]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Customer] = customerName)),
    CustomerTotal = Record.Field(#"Filtered Rows"{0},"Customer Total")
in
    CustomerTotal

Click Done to close the Advanced Editor window.

Testing the function

Let’s test the function.  Select the Data query table to use as the sourceTable and enter a Customer Name.  Click Invoke

Invoke advanced function

It should return a value

Result of invoked advanced function

Amazing stuff!!!

Using the Function

All that’s left now is for us to use the custom function.  Open the data query and add a Custom Column.  Enter the following as the formula:

=[Value] / fnCustomerTotal(Source,[Customer])
  • Source is the name of the step (i.e. use the table as it existed after the Source step has been completed)
  • [Customer] is the customer column.

Using the Custom Function

Click OK to accept the function.

Finally, undertake the following transformations:

  • Change the new column to a % data type
  • Sort by the Customer column
  • Remove the Sold By column

The Preview window will now look like this, showing the % breakdown for each transaction by customer.

Customer Percent by Product

Next Steps

If you want to take this to the next level, why not try changing the function so that it will work on any column, rather than just a column called “Customer”.

Conclusions

In this post, we’ve seen how to create basic and transformation based custom functions.  This methodology can be used any time we need a value or values which are not in the same row.  Some examples are:

  • Running totals
  • % of a whole
  • Values in previous or next rows

Manipulating the M code is difficult and prone to errors, especially when we haven’t yet built up the experience.  Therefore, like so many other features of Power Query, the best advice is to practice, practice, practice 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *