10.2 Cleaning Steps in Power Query
Everything from Module 5 is available as buttons – and recorded as repeatable steps.
| Problem | Power Query command |
|---|---|
| Extra header lines | Home › Reduce Rows › Remove Rows › Remove Top Rows, then Use First Row as Headers |
| Duplicates | select key column(s) › Home › Reduce Rows › Remove Rows › Remove Duplicates |
| Blank rows | Remove Rows › Remove Blank Rows |
| Fill down report-style blanks | Transform › Fill › Down |
| Spaces / case | Transform › Format › Trim / Clean / lowercase / UPPERCASE / Capitalize Each Word |
| City spellings | Transform › Replace Values, or merge with a mapping table (10.5) |
| ₹ and commas in amounts | Replace Values ₹ → blank, , → blank, then type Fixed decimal number |
| Split columns | Home › Split Column › By Delimiter |
| Merge columns | select columns › Transform › Merge Columns |
| Extract parts | Transform › Extract › Text Before/After Delimiter, First/Last Characters |
| Conditional column | Add Column › Conditional Column |
| Column from examples | Add Column › Column From Examples (like Flash Fill, but refreshable) |
| Errors | right-click column › Replace Errors or Remove Errors |
Steps in Excel – working with Applied Steps
- Click any step to see the data at that point.
- Rename steps (right-click › Rename) to meaningful names like
Trim City. - Delete a wrong step with the ✕; insert a step in the middle by selecting the step before it.
- Home › Advanced Editor shows the whole query as M code.
Worked example – the Module 5.18 export in Power Query: Promote headers › Uppercase Order ID › Remove Duplicates on Order ID › Replace . with - in Date and change type using locale English (India) › Merge with CityMap for City › Capitalize Each Word on Customer and Status › Replace ₹, Rs., , in Amount and set type › Close & Load. Next week: paste the new file in the same folder and Refresh.
Ravindra Bagale's Tip
When a step goes wrong in Power Query, many students rebuild the whole query. There's no need – select the wrong step and delete it, or change its settings with the gear icon. And rename your steps: "Fix Aurangabad name" is much easier to understand than "Replaced Value3".
Ravindra Bagale's Tip – मराठी
Power Query मध्ये step चुकला की बरेच students पूर्ण query परत बनवतात. गरज नाही – चुकीचा step select करून delete करा किंवा gear icon ने settings बदला. आणि step rename करा: "Replaced Value3" पेक्षा "Fix Aurangabad name" खूप समजायला सोपं आहे.
Ravindra Bagale's Tip – हिंदी
Power Query में कोई step गलत हो जाए तो बहुत से students पूरी query फिर से बनाते हैं. ज़रूरत नहीं – गलत step select करके delete करो या gear icon से settings बदलो. और steps rename करो: "Replaced Value3" से "Fix Aurangabad name" कहीं ज़्यादा समझने में आसान है.
Practice task
Rebuild the whole 5.18 cleaning in Power Query with renamed steps. Add two new messy rows to the source and refresh to prove the steps repeat correctly.