Ravindra BagaleCourses & study guides

7. Data Cleaning A–Z in Power Query

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

By Last updated:

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

Ek common chuk mhanje 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. Practice kara, mag ekdum sope vatel.