Conditional Columns

Conditional Column is the point-and-click version of a Custom Column if statement. Instead of writing M code, you fill in a dialog with dropdown menus — pick a column, pick an operator, enter a value, enter an output. For straightforward rules with a small number of branches, it is faster and less error-prone than writing M by hand. But the dialog has real limits, and knowing exactly where those limits are — and how to break through them by editing the generated M — is what this lesson is about.

The Conditional Column Dialog

Conditional Column lives on the Add Column tab. Each row in the dialog is one if/else if branch. The final row is the "else" — what to output when no condition matches. You can add as many branches as needed using the Add Clause button.

The M Code the Dialog Generates

Every Conditional Column step is stored as M code in the Applied Steps. Clicking the gear icon on the step reopens the dialog — but you can also click into the formula bar to see and edit the raw M. Knowing what the dialog generates lets you spot patterns and extend them when the UI is not enough.

// M generated by the Conditional Column dialog above
#"Added Conditional Column" = Table.AddColumn(
    #"Changed Type",
    "RevenueTier",
    each if [Revenue] >= 1500 then "Platinum"
         else if [Revenue] >= 1000 then "Gold"
         else if [Revenue] >= 500  then "Silver"
         else "Bronze"
)

// Note: the dialog always generates "else" at the end —
// this becomes the fallback value for any row that
// does not match any of the defined conditions.

All Available Operators in the Dialog

What the Dialog Cannot Do — And How to Fix It

The Conditional Column dialog is useful but deliberately limited. Six common requirements fall outside what the dialog supports — each has a workaround using the formula bar or Custom Column.

Extending the Generated M in the Formula Bar

The most productive workflow is to build the basic structure in the Conditional Column dialog, click OK, then refine the generated M in the formula bar. You get the speed of the UI for the skeleton and the full power of M for the details.

// BEFORE — what the dialog generates (column comparison as literal)
each if [Revenue] >= 1000 then "High" else "Low"

// AFTER — edited in formula bar to compare two columns
each if [Revenue] >= [Target] then "On Target" else "Below Target"

// BEFORE — dialog generates a text output
each if [Status] = "Late" then "Penalty" else "None"

// AFTER — edited to calculate a numeric output
each if [Status] = "Late"
     then try [Revenue] * 0.05 otherwise null   // 5% late penalty
     else 0

// BEFORE — dialog can only check one column per clause
each if [Revenue] >= 1000 then "Flag" else ""

// AFTER — compound condition added in formula bar
each if [Revenue] >= 1000 and [CustomerTier] = "Premium"
     then "Priority Flag"
     else if [Revenue] >= 1000
     then "High Value"
     else ""

Conditional Column vs Custom Column — Decision Guide

Practice

Lesson Quiz

Next up — Lesson 28 covers Date and Time Transformations in Power Query, including building a full Calendar table from scratch, extracting every date component needed for time intelligence, and the patterns that make DATEYTD and SAMEPERIODLASTYEAR work correctly in DAX.