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 |
About this data: Teaching sample for a fictional shop “Summit Schools”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on cleaning steps in power query.
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" कहीं ज़्यादा समझने में आसान है.
Worked example — cleaning steps in power query
Why
Neha Shah in Pune and Sophie Martin abroad both need a clear, repeatable way to use cleaning steps in power query on a GreenField Dairy sheet without breaking totals.
What
Cleaning Steps In Power Query is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for cleaning steps in power query.
- Enter a small made-up value for Neha Shah using € where money appears.
- Add a second value for Sophie Martin using ₹ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once cleaning steps in power query is correct on one row, copy the same pattern down the column.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of cleaning steps in power query on a blank sheet for Orbit Logistics.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Vikram Rao and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Liam Murphy.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply cleaning steps in power query to a 5-row made-up Orbit Logistics table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with cleaning steps in power query.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as OrbitLogist_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Rows in the import (Easy): How many rows are in the Order ID column?
- Clean one customer name (Easy): Remove the extra spaces from the first Customer name.
- Fix the case of a city (Easy): Show the first City in normal case, like "Pune".
- Total quantity (Easy): Add up the Qty column.
- How many spaces were extra? (Medium): How many extra spaces does the first Customer name have?
- Duplicate order check (Medium): How many times does Order ID "ORD-502" appear?
- Orders from one city, any case (Medium): How many rows have City "Mumbai", in any case?
- Amount by channel (Medium): Add up the Amount column for rows where Channel is "App".
- Average order amount (Medium): Find the average of the Amount column, rounded to 0 decimals.
- Rows after Remove Duplicates (Hard): How many rows are left after the extra copies of "ORD-502" are removed?
- Biggest store order (Hard): What is the largest Amount where Channel is "Store"?
- Price per unit (Hard): For the first row, divide Amount by Qty.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
