Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


How to use Power Query Custom Functions

Power Query - Custom Functions

In this post, we explore the topic of Power Query custom functions; What are they? When should we use them? And how to create them? At the time of writing, over 700 standard M functions are already available in Power Query. So why do we need custom functions at all?

Custom functions use the existing M function library and combine them to achieve a specific result. They do not create any new transformations but are a combination of existing functions. We use them where we need to apply the same transformations in many queries or multiple rows.

I thought for quite a long time about whether to include custom functions in this Power Query intro series as they can be pretty tricky. But I decided that they were too valuable to exclude. 

This post involves a bit of coding and changing the automatically generated M code, so follow along carefully.

Download the example file

I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be helpful for future reference.

Download Icon

Download the file: Power Query Custom Functions.xlsx

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. From a Power Query perspective, it is better to think of functions as any routine task that can be applied over and over again.

We’re going to tackle this area by working through two examples:

  • An easy custom function to demonstrate the syntax and format
  • A transformation-based custom function to demonstrate a real-life use case

Easy Power Query custom function example

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 from the Ribbon.

Data from Table or Range

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

In this first example, we create a simple custom function. Its purpose is to illustrate the process so that it won’t be an overwhelming learning curve when we get into the following example.

Create a blank query

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

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

Write the M code

Open the Advanced Editor for the blank query by clicking View > Advanced Editor (or Home > Advanced Editor).

The Advanced Editor window looks like this:

Blank Advanced Editor Screen

Even though it is a Blank Query, we are still provided with some initial code.

The let and in statements are key elements in M code; it tells Power Query where the transformations start and end.

Our first example aims to create a custom function that multiplies a value by 10. For this, we need a single input, which we will call initialValue.

Enter the following text into the Advanced Editor. REMEMBER: M code is case-sensitive, so type carefully 😁

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

We will deconstruct this code shortly, but for now, click Done to close the Advanced Editor dialog box.

Invoke the function

In the Power Query window, we should see the following:

Invoke a custom function

This is where we can test the function. Enter a number into the initialValue box (I’ve gone with 10) and click Invoke.

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

Result of invoked function

By invoking the function in this way, Power Query creates a query called Invoke Function. We don’t need this; it can be deleted.

Use the custom function as a new column

In the queries pane, we have the query we created at the start (which we called Data) and the function we created (called fxMyFunction).

Let’s use the custom function within our Data query. Open up the Data query and create a Custom Column by clicking Add Column > Custom Column from the Ribbon.

The code to use in the Custom Column window is:

=fxMyFunction([Value])

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

Custom Column with the Custom Function

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

Click OK to commit the function. A new column is added to the query with the result of the custom function; each result is the Value column multiplied by 10.

Custom Function result in a Custom Column

Understanding the syntax

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

The first line declares the arguments (or inputs) to be used in the function. In our example, it only has one input called initialValue.

(initialValue) =>

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

let

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

nameOfStep takes the initialValue (the argument declared in the first line) and multiplies it by 10.

nameOfStep = initialValue * 10

Finally, we return the result of the function. in denotes the end of the transformations. After the in statement, we provide the step name to be returned by the function. 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. This is 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) =>

In the code above, myText accepts only text input. To only permit a table as the argument, we would use the following:

(myTable as table) =>

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

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:
    • myText – with a text data type
    • myTable – with a table data type
  • Output has been declared as a number data type

Power Query custom functions containing transformations

Believe it or not, we now have everything we need. All it takes is a small amount of syntax and the right transformation steps.

Remember, the goal of a custom function is to repeat the same steps in multiple scenarios. The easiest way to build custom functions is to:

  1. Apply the transformation steps to calculate a single result
  2. Edit the transformations to insert parameters where the code will differ each time it runs

When you become more advanced with Power Query, you can write custom functions from scratch without recording transformations.

Example

For this example, we will calculate the total purchases for a customer.

The screenshot below contains the final result. ABC has two transactions, the first being $46 and the second being $76, but in both display $122 in the Customer Total column.

Customer Total Column

Duplicate the query

Duplicate the Data query we imported at the start. Right-click on the query in the queries list and click Duplicate in the menu.

Right-click - Duplicate Query

Rename the query

As we saw above, the name we give to the function query is the function’s name. I’ve given the query the name fxFilterTotal. Placing fx at the start is common practice for custom functions; it helps visually identify it as a function (another common prefix is fn).

Transformations

Let’s start making the transformations to calculate the result for one customer.

Start by renaming the columns we will be working with.

Rename the columns as follows:

  • Customer change to filterColumn
  • Value change to returnColumn

This is a useful step as it gives us control of column names no matter the scenario.

Next, filter on the filterColumn to select a single customer,

Filter to single value

For the final transformation, select the returnColumn and click Transform > Statistics > Sum from the Ribbon.

This will calculate the total value for the selected customer.

Final Result after transformations

There are other ways to achieve this calculation, but let’s keep it simple.

Change the query into a function

Open the advanced editor; the code will be as follows:

Advanced Editor with the initial recorded code

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

First, we must declare the arguments in the first line of the code. We need four arguments:

  1. The table to use
  2. The name of the column to filter
  3. The value to filter by
  4. The name of the column to return the result from.

Therefore, the declaration statement at the top of the query is:

(tableName as table, filterColumn as text, filterText as text, returnColumn as text) =>

Now let’s insert the arguments into our recorded code:

Change this:

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

To this:

Source = tableName,

In the following line, we change the name of the Customer and Value columns to the names of the parameters.

This:

#"Renamed Columns" = Table.RenameColumns(Source,{{"Customer", "filterColumn"}, {"Value", "returnColumn"}}),

Becomes:

#"Renamed Columns" = Table.RenameColumns(Source,{{filterColumn, "filterColumn"}, {returnColumn, "returnColumn"}}),

The final parameter to apply is the value to filter by.

This:

#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([filterColumn] = "ABC Discounts")),

Becomes

#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([filterColumn] = filterText)),

That’s all the changes. See, it wasn’t too bad, was it?

You will notice the function no longer contains any columns from the Data query we were working with. This means the function could be used in any query.

TIP: We can easily copy and paste queries between workbooks if we want to reuse them.

The completed M code should be like this:

Final Power Query Custom Functions code

Click Done to close the Advanced Editor window.

If you have any typos, here is the final text you can copy/paste.

(tableName as table, filterColumn as text, filterText as text, returnColumn as text) =>

let
    Source = tableName,
    #"Renamed Columns" = Table.RenameColumns(Source,{{filterColumn, "filterColumn"}, {returnColumn, "returnColumn"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([filterColumn] = filterText)),
    #"Calculated Sum" = List.Sum(#"Filtered Rows"[returnColumn])
in
    #"Calculated Sum"

Testing the function

Let’s invoke the function to see if it works.

  • tableName: Data
  • filterColumn: Customers
  • filterText: ABC Discounts
  • returnColumn: Value
Invoking the custom function

Click Invoke

It should return the correct value.

The result of the invoked function

Amazing stuff!!!

Use the custom 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:

=fxFilterTotal(Source,"Customer",[Customer],"Value")
  • Source – is the name of the previous step
  • “Customer” – the name of the column to filter
  • [Customer] – the value to filter by (i.e., the in from the Customer column)
  • “Value” – the column to calculate on
Custom function used a standard function

Click OK to accept the function.

The Preview window now looks like this, showing the total sales value for each customer. For example, look at ABC discounts in rows 1 and 6; they both show 122 in the Customer Total column.

Customer Total Column

Conclusion

In this post, we’ve seen how to create basic and transformation-based Power Query custom functions. 

Manipulating the M code is complex 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 🙂

Read more posts in this series

  1. Introduction to Power Query
  2. Get data into Power Query – 5 common data sources
  3. DataRefresh Power Query in Excel: 4 ways  &  advanced options
  4. Use the Power Query editor to update queries
  5. Get to know Power Query Close & Load options
  6. Power Query Parameters: 3 methods
  7. Common Power Query transformations (50+ powerful transformations explained)
  8. Power Query Append: Quickly combine many queries into 1
  9. Get data from folder in Power Query: combine files quickly
  10. List files in a folder & subfolders with Power Query
  11. How to get data from the Current Workbook with Power Query
  12. How to unpivot in Excel using Power Query (3 ways)
  13. Power Query: Lookup value in another table with merge
  14. How to change source data location in Power Query (7 ways)
  15. Power Query formulas (how to use them and pitfalls to avoid)
  16. Power Query If statement: nested ifs & multiple conditions
  17. How to use Power Query Group By to summarize data
  18. How to use Power Query Custom Functions
  19. Power Query – Common Errors & How to Fix Them
  20. Power Query – Tips and Tricks

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

2 thoughts on “How to use Power Query Custom Functions

  1. Luschi says:

    Hallo Mark,
    I’m a german PQ-Fan. My ‘fxFilterTotal’-Function don’t rename columns. I used Record.Field and Table.Columns – there can I transfer string-parameter; here is my example:
    let
    Source = tableName,
    // #”Renamed Columns” = Table.RenameColumns(Source,{{filterColumn, “filterColumn”}, {returnColumn, “returnColumn”}}),
    #”Filtered Rows” = Table.SelectRows(tableName, each (Record.Field(_, filterColumn) = filterText)),
    #”Calculated Sum” = List.Sum(Table.Column(#”Filtered Rows”, returnColumn))
    in
    #”Calculated Sum”

    kind regards Luschi

Leave a Reply

Your email address will not be published.