Create and invoke a custom function

A custom function uses the M formula language, takes a set of input values, and then returns a single output value. If you have logic that you want to reuse many times or apply the same set of transformations to a different query or value, consider creating a custom function and then invoking the function where and when you need it. There are several ways to create a custom function: 

The following is a simple example of a custom function that follows a long-held programming tradition.

  1. To create a blank query:

    Excel      Select Data > Get Data > From Other Sources > Blank Query.

    Power Query      Right click on a blank spot in the Queries pane on the left, and then select New Query > Other Sources > Blank Query.

  2. In the Queries pane on the left, double-click on the name, and then rename the new blank query to "HelloWorld".

  3. Select the new query and then select Home > Advanced Editor.

  4. Replace the template starter code with the following code:

    let
         HelloWorld = () => ("Hello World")
    in
         HelloWorld

  5. Select Done.

  6. You have changed the query, "HelloWorld", to a custom function. Note the function icon Function icon  to the left of it.

  7. To invoke the function, select it, and then select Invoke in Data Preview.

    Invoking the HelloWorld custom function

  8. The results of the function are are displayed in Data preview and added to the Queries pane as a query with the default name, Invoked Function. You might want to rename it to something more meaningful, such as "HelloWorldResult".

  9. Select that query, and then select Home > Close & Load to see the results in a worksheet.

Results 

Results of HelloWorld in a worksheet

The following example shows how to pass a parameter to a custom function to convert a decimal number to hexadecimal.

  1. To create a blank query:

    Excel      Select Data > Get Data > From Other Sources > Blank Query.

    Power Query      Right click on a blank spot in the Queries pane on the left, and then select New Query > Other Sources > Blank Query.

  2. In the Queries pane on the left, rename the new blank query to "MyHex".

  3. Select the new query and then select Home > Advanced Editor.

  4. Replace the template starter code with the following code:

    let
         MyHex = (parameter1) => Number.ToText(parameter1,"X")
    in
         MyHex

  5. Select Done.

  6. You have changed the query, "MyHex", to a custom function. Note the function icon Function icon  to the left of it.

  7. To invoke the function, select it, and then in Data Preview, enter a number in the parameter1 box, and select Invoke.

    Invoking the MyHex custom function

  8. The results of the function are displayed in Data preview and added to the Queries pane as a query with the default name, Invoked Function. You might want to rename it to something more meaningful, such as "MyHexResult".

  9. Select that query, and then select Home > Close & Load to see the results in a worksheet.

Results 

Result of the MyHex function in a worksheet

If you have created a function with at least one parameter, you can invoke it as a custom function to create a new column and a new value for each row in a table.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, edit, and load a query in Excel (Power Query).

    Note    For this example, your query needs at least one column of a Whole Number data type.

  2. Create the "MyHex" custom function as explained in the section, Create and invoke a custom function that has a parameter with the Advanced Editor.

  3. In the query, select Add Column > Invoke Custom Function. The Invoke Custom Function dialog box appears.

  4. Enter the new column name, such as "HexConvert", in the New column name box.

  5. Select the name of a pre-defined custom function from the Function query dropdown. In this example, select "MyHex".

  6. Because the custom function references a parameter, the parameter is now displayed.

  7. Select a column of a Whole Number data type as a parameter to the function.

    The Invoke Custom Function dialog box

  8. Select OK.

Result

A new column is created that shows the Hexadecimal value of the Whole Number column you entered as a parameter.

The new MyHex column of values in a worksheet

See Also

Power Query for Excel Help

Create Power Query formulas in Excel

Create a parameter query

Manage queries

Understanding Power Query M functions (docs.com)

Using custom functions (docs.com)

A subscription to make the most of your time

Need more help?

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

×