Question

How do I create a dynamic column in Power Query that calculates based on multiple conditions?
I need to create a custom column in Power Query that performs calculations based on multiple conditional rules. These conditions involve combinations of text and numeric values from other columns. What is the best method using Power Query's M language and functions like if...then...else, List.Contains, or try...otherwise to implement flexible and error-tolerant logic?

Answer 1

To create a dynamic column in Power Query that calculates based on multiple conditions, you can use Power Query’s M language with a combination of if...then...else, and, or, List.Contains, and try...otherwise constructs to build robust, rule-based logic.

Start by adding a Custom Column in the Power Query Editor. Within the formula, use nested if...then...else statements to handle each condition. For example, if your logic depends on both a text column (Category) and a numeric column (Amount), you could write:

if [Category] = "A" and [Amount] > 1000 then "High-A"
else if [Category] = "B" and [Amount] <= 500 then "Low-B"
else "Other"

For more advanced rules, use functions like List.Contains to match against lists of values, and try...otherwise to catch errors when values might be missing or invalid. For instance:

if List.Contains({"X", "Y"}, [Type]) and try [Score] > 50 otherwise false then "Pass" else "Fail"

This approach ensures your dynamic column remains flexible, scalable, and error-tolerant—ideal for complex data transformation logic before loading into the Power BI model.