Custom M Functions: Creating Reusable Components in Power Query

Image by Editor
 

Power Query’s M language allows you to create custom functions that can dramatically improve your data transformation workflows. It can automate repetitive tasks, enhance modularity, and simplify your queries.

In this tutorial, we will show how to create reusable custom M functions in Power Query.

What is an M Function?

In M language, a function is a block of code designed to perform a specific operation. A custom M function can be written to handle tasks such as cleaning data, transforming values, or performing calculations. Once created, you can reuse the function as needed, making your queries more modular and efficient.

Why Use Custom M Functions:

• Reusability: Avoid duplicating logic in multiple queries.
• Modularity: Break down complex queries into smaller, reusable components.
• Maintainability: Update logic in one place without having to adjust multiple queries.

Basic Function Structure:

Let’s start with the basic syntax for creating a function in M:

(parameter1 as type, parameter2 as type, ...) as return_type =>
expression

Create a Simple Function

Let’s begin by creating a basic function that calculates the square of a number. This simple example demonstrates how to define a function in M.

• Open Power Query Editor in Excel or Power BI.
• Go to the Home tab >> select New Source >> select Other Sources >> select Blank Query.

• Rename the query to something meaningful (e.g., “SquareNumber”)
• Go to the Home tab >> click on Advanced Editor.
• Enter the following code to create a function:

let
    SquareNumber = (num as number) as number =>
    num * num
in
    SquareNumber

• Click Done.

Explanation:

• SquareNumber: This is the name of the function.
• (num as number): This is the function’s input parameter, num, which is defined as a number type.
• num * num: The logic of the function, which calculates the square of the input number.
• as number: Indicates that the function returns a number type.

Invoke Custom Functions through Power Query Interface

Once you’ve created and loaded a custom function in Power Query, Power Query provides a user-friendly graphical interface to invoke and test your function directly.

• You’ll see your function listed in the Queries pane with an ‘fx’ icon indicating it’s a function.
• Click the function SquareNumber from the left sidebar.
• You will see the parameter input window shown below:

• Enter a numerical value in the box next to the parameter name (e.g., num).
• We inserted 4.
• Click the Invoke button.

• Power Query creates a new query automatically named Invoked Function.
• This query shows the result of your function invocation clearly. For instance, if you entered 4, the result displayed would be 16.

Interface Explanation:

• Enter Parameter: The parameter input field where you provide input values to the function.
• Invoke Button: Clicking this button will execute the function with the provided input.
• Clear Button: This resets your input.

This graphical interface makes it easy to quickly test and verify custom functions without writing additional queries.

Test Your Function

After creating the function, let’s test it in Power Query.

• Create a new query.
• Go to the Home tab >> select New Source >> select Other Sources >> select Blank Query.
• In the query editor, use your custom function to test it:

let
	TestResult = SquareNumber(4)
in
	TestResult

• Click Done.

This returns the result 16 since 4 squared equals 16.

Create a Function with Multiple Parameters

Now, let’s create a more complex function. This example will calculate the area of a rectangle by accepting both the length and width as parameters.

• Create a new query.
• Go to the Home tab >> select New Source >> select Other Sources >> select Blank Query.
• Name the Query CalculateArea.
• Open the Advanced Editor and enter the following code:

let
	CalculateArea = (length as number, width as number) as number =>
	length * width
in
	CalculateArea

• Click Done.

Explanation:

• CalculateArea: This is the name of the function.
• (length as number, width as number): Two input parameters, length and width, are defined as numbers.
• length * width: The function calculates the area of a rectangle by multiplying the length by the width.

Use the Custom Function in Queries

Now that we have a function to calculate the area of a rectangle, let’s apply it in a query.

• Suppose you have a table with columns Length and Width. You want to calculate the area for each row.
• Load a sample table or create one with Length and Width columns.
• Add a custom column.
• Go to the Add Column >> select Custom Column
• In the Custom Column dialog box;
  • Name the custom column: Area.
  • Custom column formula: Call the CalculateArea function like this:

= CalculateArea([#"Length (M)], [#"Width (M)"])

• Click OK.

This will compute the area for each row in your table using the corresponding Length and Width values.

Store Functions for Reuse

To effectively reuse your functions across multiple queries, it’s best practice to store them inside a dedicated Function Library query, structured as a record containing your functions. This approach provides a clean, organized, and reusable structure.

Create a Function Library:

• Create a Blank Query.
• Name the query FunctionLibrary.
• Open the Advanced Editor for this query and structure it like this:

let
    CalculateArea = (length as number, width as number) as number => length * width,
    SquareNumber = (num as number) as number => num * num
in
    [
        CalculateArea = CalculateArea,
        SquareNumber = SquareNumber
    ]

• Click Done.

Explanation:

• Function Definitions: First, define each custom function clearly.
• Record Structure [ ]: At the end, wrap your functions within square brackets to create a record, mapping function names to the actual functions. This record acts like a “library” of your custom functions.

Use Functions from the Function Library in Other Queries:

Now you can reference these functions seamlessly from other queries using the proper syntax.

Syntax:

FunctionLibrary[FunctionName](parameters)

Here’s how to reference your stored functions in another query:

let
    Source = Table.FromRecords({[Length=4, Width=5], [Length=6, Width=7]}),
    AddedColumn = Table.AddColumn(Source, "Area", each FunctionLibrary[CalculateArea]([Length], [Width])),
    Area = AddedColumn{0}[Area]
in
    Area

This correctly applies your stored functions, producing column “Area”.

Note: Power Query doesn’t support dot (.) notation for referencing functions unless explicitly structured in modules or external libraries. Therefore, always use square brackets ([]) to reference functions stored in records.

Error Handling in Functions

Error handling is crucial to prevent your queries from failing unexpectedly. You can use the try…otherwise construct to handle errors gracefully.

Here’s an example of modifying the SquareNumber function to handle cases where the input is not a number:

let
	SquareNumber = (num as any) as number =>
	try num * num otherwise 0
in
	SquareNumber

In this version, if the input is not a number, the function will return 0 instead of throwing an error.

Advanced Custom Functions

You can make your custom M functions even more powerful by incorporating advanced features such as:

Optional Parameters

If you want to create a function with optional parameters, you can set default values.

let
    GreetPerson = (name as text, greeting as text) as text =>
    if greeting = null then "Hello, " & name else greeting & ", " & name
in
    GreetPerson

If the greeting parameter is omitted, it defaults to “Hello”.

Recursive Functions

M supports recursion, allowing you to define functions that call themselves. This is particularly useful for iterative operations like calculating factorials.

let
    Factorial = (n as number) as number =>
    if n <= 1 then 1 else n * @Factorial(n - 1)
in
    Factorial

Use Lists and Records

You can create functions that work with lists or records. For example, here’s a function that sums a list of numbers:

let
    SumList = (numbers as list) as number =>
    List.Sum(numbers)
in
    SumList

Advanced Function Techniques

Type Handling:

Ensure your functions handle different data types gracefully:

(value as any) as text =>
let
    Result = 
        if value = null then 
            ""
        else if Type.Is(Value.Type(value), type text) then
            value
        else
            Text.From(value)
in
    Result

Documentation:

Document your functions for easier maintenance:

/* 
    Function: FormatPhoneNumber
    Description: Formats a numeric phone number into (xxx) xxx-xxxx format
    Parameters:
        - phoneNumber: A numeric or text representation of a phone number
    Returns: Formatted phone number as text
*/
(phoneNumber as any) as text =>
let
    // Implementation
in
    Result

Conclusion

Custom M functions in the Power Query can ease the data transformation process, enhance reusability and simplify maintenance. Whether you’re performing simple calculations or more complex operations, custom functions are an excellent way to keep your Power Query projects organized and efficient. Custom M functions are a powerful tool that can save time, reduce errors, and make your data processing much more manageable.