Skip to content

11. Conditional Columns

Goal: Create a category column with if/else logic. No formula typing is needed.

A conditional column is the Power Query way to write an IF formula. You fill a form. Power Query writes the M code for you.

Step 1: Add the column

  1. Click the Location table.
  2. Click Add Column in the ribbon.
  3. Click Conditional Column.

The Add Conditional Column window opens.

Conditional Column window The Add Conditional Column window.

Step 2: Define the first condition

  1. Type the new column name: Category.
  2. Set the first condition:
  3. Column name: Population
  4. Operator: is less than
  5. Value: 100000
  6. Output: Low density
  7. Click Add clause.

Step 3: Add the second condition

  1. Set the next condition:
  2. Column name: Population
  3. Operator: is greater than
  4. Value: 500000
  5. Output: High density
  6. The else value: Medium density.

Power Query fills any row that matches no condition with the else value.

  1. Click OK.

The result

Population Category
80,000 Low density
250,000 Medium density
600,000 High density
(empty) Medium density

Error after the merge

Some population values are empty (null) after the merge. These rows show an error in the Category column.

Step 4: Fix the error rows

  1. Right-click the Category column header.
  2. Click Replace Errors.
  3. Type No data in the value box.
  4. Click OK.

The errors become the text "No data". The column has no more errors.

Replace Errors window The Replace Errors window. Type No data in the Value box.

Trainer screenshot

The trainer shares this screenshot in the Zoom chat. Save it for your notes.

Conditional column — trainer view The trainer's screen at the "share screenshot" moment: the conditional column result with the error rows.

Other error fixes

You can also filter out the error rows or remove them. Use Replace Errors when you want to keep all rows.

Next: Continue to 12. Data Model.