Question

How do I create a Power Query function that dynamically adjusts based on input parameters?
I need a Power Query function that modifies its transformations based on input parameters. How can I design a dynamic function that adapts its behavior based on user-defined inputs to ensure flexibility and reusability?

Answer 1

If you want to build a Power Query function that can dynamically respond to input parameters, you must define a function that takes parameters to modify its transformation logic conditionally. Other approaches are given as follows:

1. Very Basic Dynamic Function for Filtering Data

You could build a function that filters a table according to some chosen column and value defined by the user:

(SourceTable as table, ColumnName as text, FilterValue as any) =>  
let  
    FilteredTable = Table.SelectRows(SourceTable, each Record.Field(_, ColumnName) = FilterValue)  
in  
    FilteredTable  

 FilteredTable  
Usage: DynamicFilter(Data, "Category", "Electronics") will return only rows where the "Category" column is "Electronics."

2. Function to Select Specific Columns Dynamically
This function allows users to choose which columns to keep:

(SourceTable as table, ColumnsToKeep as list) =>  
let  
    SelectedColumns = Table.SelectColumns(SourceTable, ColumnsToKeep, MissingField.Ignore)  
in  
    SelectedColumns  

Usage:

SelectColumnsDynamic(Data, {"Product", "Sales"}) will return only the "Product" and "Sales" columns.

3. Dynamic Transformation Function Based on User Inputs

This function allows users to apply different transformations (e.g., changing case, replacing values) dynamically

(SourceTable as table, ColumnName as text, Operation as text) =>  
let  
    TransformedTable =  
        if Operation = "Uppercase" then  
            Table.TransformColumns(SourceTable, {{ColumnName, Text.Upper}})  
        else if Operation = "Lowercase" then  
            Table.TransformColumns(SourceTable, {{ColumnName, Text.Lower}})  
        else  
            SourceTable  
in  
    TransformedTable  

Usage:

DynamicTransform(Data, "CustomerName", "Uppercase") converts all values in "CustomerName" to uppercase.