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: 

• Use the Advanced Editor to add your own let statement and start from scratch.
• Use the Invoke Custom Function command.
• There are additional ways to create functions not discussed in this help topic including the Create Function and Add as a Query commands. For an in-depth discussion, see Understanding Power Query M functions and Using custom functions.

Create and invoke a simple custom function with the Advanced Editor

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

1. 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 changed the query, HelloWorld, to a custom function. Note the function icon  to the left of it.

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

   

8. The results of the function appear in Data Preview and are 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

Create and invoke a custom function that has a parameter with the Advanced Editor

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

1. 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 changed the query, MyHex, to a custom function. Note the function icon  to the left of it.

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

8. The results of the function appear in Data Preview. Power Query adds the results 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

Add a column by invoking a custom function

If you create 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.

   

8. Select OK.

Result

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

See also

Power Query for Excel Help

Create a parameter query (Power Query)

Manage queries (Power Query)

Understanding Power Query M functions

Using custom functions