Ravindra BagaleCourses & study guides मराठी Track your progress

8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns

8.5 Conditional Column

By Last updated:

Start with a problem

Zoya needs an Order Size label (Large/Medium/Small) without writing M.

Explain simply

Conditional Column builds if/else rules in a dialog. Rules are checked top to bottom; the first match wins.

Show the data

Order ID Amount
201 500
202 300
203 700
204 400
205 0

About this data: This table uses columns: Order ID, Amount. This table uses columns: Order Size, Orders, Amount (₹). This table uses columns: Order ID, Amount (₹). This table uses columns: Order ID, Amount (₹), Basket Band. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: column_vs_measure_orders.csv (source: src/practice/).

Demonstrate

  1. Add Column › Conditional Column › name Order Size.
  2. Rule 1: Amount is greater than or equal to 500 → Large.
  3. Rule 2 (Add Clause): Amount ≥ 300 → Medium. Else → Small.

  4. OK; set type Text.

    Your result should look like this:

    Order Size
    Large, Medium, Large, Medium, Small

Show the result

Conditional column rules for order size, sample layout with fictional data

Your result should look like this (fictional sample):

Order Size Orders Amount (₹)
Large 2 1,200
Medium 2 700
Small 1 0

Explain what happened

Each row is tested against rule 1, then rule 2, then Else.

Explore further

  • Use a mapping table for many categories.

Detailed reference for this lesson:

Conditional column in Power Query with example — in 30 seconds

What: A conditional column gives each row a label using if / else rules.

Why: A slicer like "Below ₹199 / ₹199–498 / ₹499+" needs a column, not a measure.

Tiny example: ₹150 → Below ₹199. ₹320 → ₹199–498. ₹650 → ₹499+.

First look at the data (before → after):

Conditional column in Power Query with example: before and after tables, fictional Blinkit Maharashtra data

Same before / after tables as text

Before — Order lines

Order ID Amount (₹)
BLK-1 150
BLK-2 320
BLK-3 650

Formula / click path

Add Column › Conditional Column
If Amount < 199 → "Below ₹199"
Else if Amount < 499 → "₹199–498"
Else → "₹499+"

After — After the conditional column

Order ID Amount (₹) Basket Band
BLK-1 150 Below ₹199
BLK-2 320 ₹199–498
BLK-3 650 ₹499+

Do it step by step:

  1. Click Add Column › Conditional Column.
  2. New column name: Basket Band.
  3. Rule 1: Amount is less than 199 → Below ₹199.
  4. Click Add Clause. Rule 2: Amount is less than 499 → ₹199–498.
  5. Else: ₹499+. Click OK.
  6. Set the new column type to Text.

If–then–else rules without code. Covered with a data-quality example in Module 7.16. Here is a business example, Basket Size:

Steps in Power BI

  1. Add Column › Conditional Column › New column name: Basket Size.
  2. If Amount is less than 199 → Small.
  3. Add Clause: Else If Amount is less than 499 → Medium.
  4. Else → Large › OK.
  5. The Operator list changes by type (text: begins with, contains, equals; numbers: is greater than …). The Value/Output boxes can also refer to a column or a parameter (click the ABC123 drop-down).

Aata pudhe jaauya Index Column kade.

Ravindra Bagale's Tip

Friends, remember: rules are checked top to bottom, and the first match wins. Writing "< 499 → Medium" before "< 199 → Small" makes every small basket Medium. Order the rules from the smallest threshold upward and test boundary values like 199 and 200. Remember this rule.

Fix common mistakes

Wrong result Reason Correction
Everything Medium Rule ≥ 300 placed first Put the strictest rule first
Error for null amounts Null compared Add an equals null rule first
500 labelled Medium Used greater than not ≥ Check operators

Practise independently

Practice task

Add rule Amount = 0 → Cancelled at the top. What changes?

Hints
  • Order 205.
Solution

205 becomes Cancelled instead of Small; other rows unchanged.

Think like a professional

Agree band boundaries (inclusive or exclusive) with the business and write them down.

Ravi Sir's Cards for this lesson

Conditional Column. Reports need groups like Weekday or Weekend, which are not in the data.Revision cardConditional Column vs DAX IF column. Both add an if-then label to each row. A Conditional Column is made in Power Query with a form. A DAX IF column is written as a calculated column.Comparison card

मराठी cards