Power BI - Custom Functions in Power Query M to Reuse Blocks of Code

Power BI's data transformation has two interfaces, there is the Graphical UI where you can select different preselected steps to put them in a certain order and there's also the M code editor where you can dive straight into the code behind the transformation to craft the exact transformation that you need.

If you're working with a rather complex report with several different data sources, you may need to repeat the same steps multiple times. You can repeat the same block of M code by creating a custom function in the M code editor, this can be done even if you created those steps using the graphical UI because choosing steps in the UI creates lines of M code in the editor.

This custom M code function makes the code reusable and more efficient.

How Do Functions Work in M?

A function in Power Query M is a block of reusable code that takes input parameters, takes those inputs through a series of calculations and transformations, and then returns the result as an output.

Here is the structure of a custom function:

functionName = (parameter1 as type, parameter2 as type) =>
    let
        step1 = some transformation,
        step2 = another transformation
    in
        step2

functionName This is the name of the function to be used later when you call the function.

(parameter1 as type, parameter2 as type) The input parameters are in a bracketed comma-separated list with each parameter's data type also being defined.

let
    ...
    ...
in

The steps between the let and in are the repeatable block of code.

in
    ...

The last line after in defines which step from the list of steps should be the output, this is almost always the last step.

Example: A Function to Double a Number

double = (someNumber as number) =>
    let
        step1 = someNumber * 2
    in
        step1

Calling the Function

result = double(5) // input 5 into the function to output 10

How Does it Look Put Together?

let  
    double = (someNumber as number) =>  
        let  
            step1 = someNumber * 2  
        in  
            step1,  

    result = double(5)
in  
    result