Question

How can I create a function in Power Query that processes data differently based on a user-selected parameter?
I need to create a custom function in Power Query that adjusts its behavior based on a user-defined parameter. The function should apply different transformations or calculations depending on the selected parameter value. What is the best way to implement this using M language, and how can I ensure the function is reusable and dynamic within my dataset?

Answer 1

The following steps outline the creation of a dynamic function in Power Query that accommodates different processing logic based on a parameter defined by the user:

1. Define a Parameter in Power Query

In this case, the parameter would be defined under Manage Parameters, where the input option in the parameter can be selected "Sum," "Average," or "Max" for predefined settings.

2. Create the Custom Function

An M function is to be defined as one that accepts the parameter as input and applies different logic concerning the value.

Sample function:

(data as table, columnName as text, operation as text) =>  
let  
    result =  
        if operation = "Sum" then List.Sum(data[columnName])  
        else if operation = "Average" then List.Average(data[columnName])  
        else if operation = "Max" then List.Max(data[columnName])  
        else error "Invalid operation"  
in  
    result  

3. Apply the Function in a Query

• Use the function dynamically in your query, passing the parameter value:

MyFunction(SourceTable, "Sales", SelectionMode)

This ensures that as users modify SelectionMode, the calculation updates accordingly.