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

7. Data Cleaning A–Z in Power Query

7.24 Conditional Column, Index Column and Column From Examples in Cleaning

By Last updated:

Start with a problem

Shraddha wants a Delivery Band column, a row number to trace rows to the source file, and a Month Name column without remembering the M function.

Explain simply

Conditional Column applies if/else rules top to bottom. Index Column numbers rows. Column From Examples writes the formula from an example you type.

Show the data

Order ID Order Date Delivery Mins
301 06-Apr-2026 8
302 06-Apr-2026 12
305 04-May-2026 7
308 05-May-2026 null

About this data: This table uses columns: Order ID, Order Date, Delivery Mins. This table uses columns: Band (all 8 orders), Orders. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

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

Demonstrate

  1. Add Column › Conditional Column Delivery Band: if Delivery Mins equals null → "Not delivered"; else if ≤ 10 → "Within 10 min"; else "Over 10 min".

    Your result should look like this:

    Delivery Band
    Within 10 min, Over 10 min, Within 10 min, Not delivered

  2. Add Column › Index Column › From 1.

    Your result should look like this:

    Index
    1, 2, 3, 4

  3. Add Column › Column From Examples › From Selection (Order Date) › type April in row 1 › Enter.

    Your result should look like this:

    Month
    April, April, May, May

Show the result

Your result should look like this (fictional sample):

Band (all 8 orders) Orders
Within 10 min 3 (301, 304, 305)
Over 10 min 4 (302, 303, 306, 307)
Not delivered 1 (308)

Explain what happened

Rules run top to bottom; checking null first avoids an error, because in M null <= 10 returns null and an if-condition cannot be null.

Explore further

  • Column From Examples shows the M it generated (here Date.MonthName) – read and learn it.

Detailed reference for this lesson:

These three Add Column tools are used all the time while cleaning. Module 8 covers every Add Column option in detail. Here is how they help with cleaning:

  • Conditional Column: data-quality flags (7.16) and standardised buckets, e.g. Payment Group = "Digital" for UPI/Card, otherwise "Cash".
  • Index Column (From 1): gives each row a number before you sort or remove duplicates, so you can trace a row back to the source file ("row 57 of the Pune file has a bad date").
  • Column From Examples: type "Kothrud" next to "Blinkit Kothrud (Pune)" and Power Query suggests Text Between Delimiters. Great when you do not remember the function name.

Ravindra Bagale's Tip

A common mistake is building a long conditional column when a mapping table would be clearer, or forgetting that rules are checked top to bottom. Keep conditional columns short and ordered from the most specific rule to the most general. For more than five or six categories, use a mapping table and Merge. Practise, and it will feel very easy.

Fix common mistakes

Wrong result Reason Correction
308 shows Error No null rule; null comparison gives null Put equals null first
Index changes after refresh Added after sort Add Index before sorting
Examples guess wrong Too few examples Type a second example

Practise independently

Practice task

Add a rule ≤ 5 → Super fast above ≤ 10. Which orders are Super fast?

Hints
  • None below 6?
Solution

None – the fastest is 305 at 7 minutes.

Think like a professional

Prefer a mapping table when rules grow beyond 4–5 lines.