Create Custom M Function in Power Query

In this exercise, we will learn how to create a custom M function in power query.
The function has the following syntax:

Syntax

let
    FunctionName = (Parameter1 as Type1, Parameter2 as Type2, ...) as ReturnType => Expression
in
    FunctionName as Type3

The type information of the parameters and the function return value are optional.

Example: Basic Custom Function. Let’s say we want to create a custom function to calculate the square of a number.

Step 1: Open the Advanced Editor

Go to Power Query Editor.
In the Home tab, click on Advanced Editor.

Step 2: Create the Function

Paste the following code into the Advanced Editor:

Power Query M

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

Explanation:

This function takes a single parameter (number) of type number.
It returns the square of the given number of datatype number.

Use of created function

We can use the function to add a column in the table. Here, the function with iterate over each row of the table.

Step 1: Click on Add Column and then click on Invoke Custom Function.

We can specify the new column name and then select the function query. Here, the first_function is the query name of the function that we have created previously.

The function will create a new column in the table. The function will run for each run in the table.

The power query code is shown below:

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Phone = "123-4567", Company = "TCS"], 
      [CustomerID = 2, Name = "Katrina", Phone = "987-6543", Company = "TCS"], 
      [CustomerID = 3, Name = "Alia", Phone = "543-7890", Company = "TCS"], 
      [CustomerID = 4, Name = "Vicky", Phone = "676-8479", Company = "TCS"], 
      [CustomerID = 5, Name = "Mohini", Phone = "574-8864", Company = "TCS"], 
      [CustomerID = 6, Name = "Meenakshi", Phone = "574-8864", Company = "TCS"], 
      [CustomerID = 7, Name = "Esha", Phone = "574-8864", Company = "TCS"], 
      [CustomerID = 8, Name = "Anjali", Phone = "574-8864", Company = "TCS"]
    }
  ),
    return = Table.AddColumn(MyTable, "Squared Values", each first_function([CustomerID]))
in
    return

Function with Two Arguments

Let’s create a custom Power Query function with two arguments. I'll show a few examples to demonstrate how we can use multiple parameters in a custom function.

Example: Basic Function (Adding Two Numbers) We'll create a function that takes two numbers as inputs and returns their sum.

Step 1: Open the Advanced Editor In the Power Query Editor, go to Advanced Editor and paste the following code:

Power Query M

let
    AddNumbers = (number1 as number, number2 as number) as number =>
    number1 + number2
in
    AddNumbers

Explanation:

Inputs: number1 and number2 of type number.
Output: The function returns the sum of both inputs.

Example: Find the first element of a list greater than 5, or null otherwise.

Power Query M

let
    FirstGreaterThan5 = (list) =>
        let
            GreaterThan5 = List.Select(list, (n) => n> 5),  
            First = List.First(GreaterThan5)  
        in
            First,
    Results =
    [
            Found    = FirstGreaterThan5({3,7,9}),  // equals 7
            NotFound = FirstGreaterThan5({1,3,4})   // equals null
    ]
in
    Results

The output of the above code is shown below:

Previous Next