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
Khup students Power Query madhe step chukla ki poori query parat banvtat. Garaj nahi – chukicha step select karun delete kara kiwa gear icon ne settings badla. Ani step rename kara: "Replaced Value3" peksha "Fix Aurangabad name" khup samjayla sope aahe.
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.