Conditional Logic and Dynamic Column Selection in Power Query M: Mastering if-then-else, each, and Predicate Functions

Introduction

Picture this: you're building a Power Query transformation for a sales reporting dataset, and your source data has an annoying habit — some months the file includes a column called "Revenue_USD", other months it's called "Revenue", and occasionally a well-meaning colleague decided to name it "Rev_USD". Your query breaks every time the column name shifts, and you're tired of manually patching it.

Or consider a more ambitious scenario: you need to classify every row in a transaction table as "High Value," "Medium Value," or "Low Value" based on the transaction amount — but the thresholds change depending on the product category. Static formulas won't cut it. You need logic that thinks.

This is where conditional logic in Power Query's M language becomes one of your most powerful tools. By the end of this lesson, you'll understand how M handles decision-making at the row level, how the mysterious each keyword connects to that logic, and how predicate functions let you make dynamic decisions about entire columns — including selecting, filtering, or renaming columns based on conditions rather than hardcoded names.

What you'll learn:

• How if-then-else expressions work in M and how they differ from Excel's IF function
• How to nest conditions and use and/or to build multi-branch logic
• What each actually is and why it's not just "syntax sugar"
• How to use predicate functions with Table.SelectColumns, Table.RemoveColumns, and List.Select for dynamic column selection
• How to combine all of these techniques to write queries that adapt to changing data structures

---

Prerequisites

You should be comfortable opening the Power Query Editor, creating basic M expressions in the formula bar, and reading a simple let...in expression. You don't need to be an M expert — this lesson builds from fundamentals — but if you've never opened the Advanced Editor, spend ten minutes doing so on any query before proceeding. You should also understand what a list and a table are in M at a conceptual level.

---

The Anatomy of if-then-else in M

Let's start at the very beginning, because M's conditional syntax surprises people coming from Excel or Python.

In Excel, you write: =IF(A2>1000, "High", "Low")

In M, the equivalent expression is:

if [Amount] > 1000 then "High" else "Low"

Notice a few things immediately. First, it's lowercase — if, then, and else are all lowercase keywords. M is case-sensitive, so If or IF will throw an error. Second, there are no parentheses wrapping the condition. Third — and this is critical — there is no optional else in M. Every if expression must have both a then branch and an else branch. If you want to do nothing in one case, you still have to write else null.

Here's what this looks like inside a real query step. Suppose you have a sales table and you want to add a classification column:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    AddedClassification = Table.AddColumn(
        Source,
        "ValueTier",
        each
            if [Amount] > 10000 then "High Value"
            else if [Amount] > 2500 then "Medium Value"
            else "Low Value"
    )
in
    AddedClassification

That else if pattern is how you chain multiple conditions. M doesn't have a switch or case statement built in as a keyword — you build multi-branch logic by nesting else if blocks. It reads naturally from top to bottom: check the first condition, and if it's false, fall through to the next test.

Important: M evaluates conditions lazily from top to bottom. Once it finds a true branch, it stops checking. This means the order of your conditions matters. In the example above, if you put the > 2500 check first, every transaction over 10,000 would also be caught by it and never reach the "High Value" branch.

---

Using and / or in Conditions

Real-world classification rules almost always involve multiple criteria. M uses the keywords and and or (again, lowercase) to combine conditions, exactly as you'd expect logically.

Imagine you want to flag transactions that are both high-value and from the "Enterprise" segment:

if [Amount] > 10000 and [Segment] = "Enterprise" then "Priority" else "Standard"

Or perhaps you want to catch any transaction that's either a refund or has a negative amount:

if [TransactionType] = "Refund" or [Amount] < 0 then "Needs Review" else "OK"

When combining and and or, M follows standard Boolean precedence — and binds more tightly than or. To make your intent explicit and your code readable, use parentheses:

if ([Amount] > 10000 and [Segment] = "Enterprise") or [TransactionType] = "Override"
then "Priority"
else "Standard"

Tip: Always use parentheses when mixing and and or. It costs you nothing and prevents bugs that are genuinely difficult to track down.

---

Demystifying `each`: What It Actually Is

Here's the concept that confuses more intermediate M learners than almost any other: each.

You see it everywhere. Table.SelectRows(Source, each [Amount] > 0). List.Select(myList, each _ > 5). Table.AddColumn(Source, "NewCol", each [A] + [B]). It looks like a keyword with magical powers. It isn't.

each is shorthand for a function declaration. In M, functions are written like this:

(x) => x + 1

That's a function that takes one argument x and returns x + 1. Now, many M functions that operate row-by-row need you to pass a function as an argument — a function that describes what to do for each record. Typing out (row) => row[Amount] > 0 every time would be tedious, so M provides a shortcut:

• each replaces (x) =>
• The underscore _ refers to the implicit argument

So these two expressions are exactly identical:

(row) => row[Amount] > 0
each [Amount] > 0

Inside an each expression, square bracket access like [Amount] is shorthand for _[Amount] — it accesses the field named "Amount" on whatever the implicit argument is. When used with Table.SelectRows, the implicit argument is each row (as a record). When used with List.Select, the implicit argument is each list item.

This is why each _ > 5 works on a list but each [Amount] > 0 works on a table — in the list context, the item itself is _, not a record with named fields.

Let's see this in action with a concrete table filtering example:

let
    Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
    FilteredRows = Table.SelectRows(
        Source,
        each [Amount] > 0 and [Status] = "Completed"
    )
in
    FilteredRows

The each here is passing an anonymous function to Table.SelectRows. That function is called once for every row in Source, and it returns true or false. Rows where it returns true are kept; the rest are removed.

---

Predicate Functions: Making Decisions About Columns

A predicate function is simply a function that takes one input and returns true or false. You've already seen them — each [Amount] > 0 is a predicate. Predicates are most powerful when you use them to make structural decisions about your table — which columns to keep, remove, or rename — rather than row-level decisions.

Dynamic Column Selection with Table.SelectColumns

The standard way to select specific columns in M is:

Table.SelectColumns(Source, {"OrderID", "CustomerName", "Amount"})

This works until your source changes column names, which it will. The dynamic alternative is to use List.Select with a predicate to filter the list of column names rather than hardcoding them.

First, get the list of all column names from the table:

Table.ColumnNames(Source)
// Returns something like: {"OrderID", "CustomerName", "Amount", "Cost", "Margin", "Internal_Flag"}

Now use List.Select with a predicate to keep only the columns you want:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    AllColumns = Table.ColumnNames(Source),
    KeepColumns = List.Select(AllColumns, each not Text.StartsWith(_, "Internal_")),
    FinalTable = Table.SelectColumns(Source, KeepColumns)
in
    FinalTable

Walk through what's happening here. List.Select takes the list of column names and the predicate each not Text.StartsWith(_, "Internal_"). For each column name (the _), it checks whether the name starts with "Internal_". If it doesn't (because of not), the column name is kept. The result KeepColumns is a filtered list of column names, which is then passed directly to Table.SelectColumns.

This query will automatically adapt if the source adds new non-internal columns or renames existing internal ones.

Tip: List.Select is your most versatile tool for dynamic column work. Any condition you can express about a column name — string patterns, length, whether it contains a keyword — can become a column selector.

Handling the "Missing Column" Problem

Back to the opening scenario: column names that change between runs. Here's a robust pattern using if-then-else combined with List.Contains:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    AllColumns = Table.ColumnNames(Source),
    RevenueColumn =
        if List.Contains(AllColumns, "Revenue_USD") then "Revenue_USD"
        else if List.Contains(AllColumns, "Revenue") then "Revenue"
        else if List.Contains(AllColumns, "Rev_USD") then "Rev_USD"
        else error "No recognizable revenue column found in source data",
    RenamedTable = Table.RenameColumns(Source, {{RevenueColumn, "Revenue_Normalized"}}),
    FinalTable = Table.SelectColumns(RenamedTable, {"OrderID", "CustomerName", "Revenue_Normalized"})
in
    FinalTable

The error keyword in M lets you throw a meaningful error message instead of silently failing. Notice how RevenueColumn is just a variable that holds a column name string — derived through conditional logic. It's then used as a value inside Table.RenameColumns. This is the key insight: column names are just strings, and you can compute them dynamically.

---

Combining Conditions and each for Row-Level Computed Columns

Now let's combine everything for a realistic multi-condition scenario. Suppose you have a transaction table with these columns: Amount, Category, Region, and CustomerTier. Your business rule is:

• If CustomerTier is "Platinum" and Amount > 5000, apply a 15% discount
• If CustomerTier is "Gold" and Amount > 5000, apply a 10% discount
• If Region is "APAC" and Category is "Software", apply a 5% discount
• Otherwise, no discount

let
    Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
    AddedDiscount = Table.AddColumn(
        Source,
        "DiscountRate",
        each
            if [CustomerTier] = "Platinum" and [Amount] > 5000 then 0.15
            else if [CustomerTier] = "Gold" and [Amount] > 5000 then 0.10
            else if [Region] = "APAC" and [Category] = "Software" then 0.05
            else 0,
        type number
    ),
    AddedFinalAmount = Table.AddColumn(
        AddedDiscount,
        "FinalAmount",
        each [Amount] * (1 - [DiscountRate]),
        type number
    )
in
    AddedFinalAmount

A few things worth calling out. First, the optional fourth argument to Table.AddColumn is the column type — specifying type number is good practice because it helps Power Query avoid downstream type inference overhead. Second, notice that AddedFinalAmount references [DiscountRate], which is the column we just created in the previous step. Each step in an M query builds on the previous, so you can chain computed columns this way without any issues.

---

Hands-On Exercise

Open Power Query Editor (in Excel: Data tab → Get Data → Launch Power Query Editor, or in Power BI Desktop: Home tab → Transform Data). Create a blank query (Home → New Source → Blank Query), open the Advanced Editor, and paste in the following code to create a sample dataset:

let
    Source = Table.FromRecords({
        [OrderID = 1001, Customer = "Northwind", Segment = "Enterprise", Amount = 15000, Region = "NA",    Status = "Completed",  Internal_Score = 88],
        [OrderID = 1002, Customer = "Contoso",   Segment = "SMB",        Amount = 800,   Region = "EMEA",  Status = "Pending",    Internal_Score = 42],
        [OrderID = 1003, Customer = "Fabrikam",  Segment = "Enterprise", Amount = 22000, Region = "APAC",  Status = "Completed",  Internal_Score = 95],
        [OrderID = 1004, Customer = "AdventureW", Segment = "SMB",       Amount = 3200,  Region = "NA",    Status = "Completed",  Internal_Score = 61],
        [OrderID = 1005, Customer = "Litware",   Segment = "Mid-Market", Amount = 9500,  Region = "EMEA",  Status = "Cancelled",  Internal_Score = 77]
    })
in
    Source

Now extend this query step by step in the Advanced Editor to accomplish the following tasks:

Task 1: Add a column "DealTier" that classifies each row:

• "Strategic" if Segment is "Enterprise" and Amount > 12000
• "Growth" if Amount > 5000 (and not already Strategic)
• "Standard" otherwise

Task 2: Using List.Select and Table.ColumnNames, remove any columns whose names start with "Internal_" without hardcoding column names.

Task 3: Filter the table to only include rows where Status is "Completed".

Try to write each step yourself before checking below.

Solution:

let
    Source = Table.FromRecords({
        [OrderID = 1001, Customer = "Northwind",  Segment = "Enterprise", Amount = 15000, Region = "NA",   Status = "Completed", Internal_Score = 88],
        [OrderID = 1002, Customer = "Contoso",    Segment = "SMB",        Amount = 800,   Region = "EMEA", Status = "Pending",   Internal_Score = 42],
        [OrderID = 1003, Customer = "Fabrikam",   Segment = "Enterprise", Amount = 22000, Region = "APAC", Status = "Completed", Internal_Score = 95],
        [OrderID = 1004, Customer = "AdventureW", Segment = "SMB",        Amount = 3200,  Region = "NA",   Status = "Completed", Internal_Score = 61],
        [OrderID = 1005, Customer = "Litware",    Segment = "Mid-Market", Amount = 9500,  Region = "EMEA", Status = "Cancelled", Internal_Score = 77]
    }),
    AddedTier = Table.AddColumn(
        Source,
        "DealTier",
        each
            if [Segment] = "Enterprise" and [Amount] > 12000 then "Strategic"
            else if [Amount] > 5000 then "Growth"
            else "Standard",
        type text
    ),
    AllColumns = Table.ColumnNames(AddedTier),
    PublicColumns = List.Select(AllColumns, each not Text.StartsWith(_, "Internal_")),
    RemovedInternal = Table.SelectColumns(AddedTier, PublicColumns),
    FilteredCompleted = Table.SelectRows(
        RemovedInternal,
        each [Status] = "Completed"
    )
in
    FilteredCompleted

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting that M is case-sensitive Writing If, Then, or Else will produce a cryptic error like "Token Identifier expected." Always use lowercase if, then, else.

Mistake 2: Missing the else branch M requires every if to have an else. If you write if [Amount] > 0 then "Positive" with no else, the parser will throw an error. Add else null if you genuinely want nothing in the false case.

Mistake 3: Using each where you need a full function each [Amount] works inside table operations because _ is a record. But if you need to reference two different scopes or pass a parameter, you need the full (x) => ... syntax. Trying to use each to do something like capture an outer variable can produce unexpected results.

Mistake 4: Column name typos in conditions If you write [Ammount] instead of [Amount], M will throw an error like "The column 'Ammount' of the table wasn't found." Double-check names against Table.ColumnNames(Source) when debugging.

Mistake 5: Applying List.Select but forgetting to use the result A common pattern error: computing PublicColumns with List.Select but then never passing it to Table.SelectColumns. The computation is wasted. Make sure every intermediate variable feeds into the final result chain.

Mistake 6: Expecting short-circuit safety on null values If a column contains null, comparisons like [Amount] > 1000 will return null (not false) in M, which means rows might behave unexpectedly in filters. Use if [Amount] = null then "Unknown" else if [Amount] > 1000 then "High" else "Low" to handle nulls explicitly before other comparisons.

---

Summary & Next Steps

Let's bring it all together. You've learned that:

• if-then-else in M is an expression (not a statement), always requires an else branch, and chains into else if blocks for multi-condition logic
• and and or build compound conditions; parentheses make your intent explicit
• each is shorthand for (_) =>, and inside it, [ColumnName] is shorthand for _[ColumnName] — understanding this lets you read and write M fluently instead of just copying patterns
• Predicate functions (functions returning true/false) passed to List.Select enable dynamic column selection based on column name patterns, eliminating hardcoded column lists
• Combining List.Contains with if-then-else lets you write queries that gracefully handle schema variation in source data

These techniques sit at the intersection of M's functional design and real-world data messiness. Once you internalize that columns are just strings, functions are just values, and each is just a shorthand — the whole language becomes dramatically more readable.

Where to go next:

• List.Transform — like List.Select but applies a transformation to each item instead of filtering. Combine with each and string functions to rename columns in bulk.
• Table.TransformColumns — applies column-specific transformations using a list of {columnName, transformFunction} pairs, ideal for dynamic type conversion.
• Custom functions — once you're comfortable writing (x) => functions, you can define reusable functions at the top of your query or in a separate query, unlocking real modular M development.
• Value.ReplaceType and type inference — understanding how M propagates types through conditional branches will help you avoid silent type coercion bugs in production queries.

The logic skills you've built here aren't just for Power Query — they're the foundation for writing clean, defensive, maintainable M code at scale.