Why Power Query Beats Manual Cleaning
Most Excel assignments lose marks not on analysis, but on dirty data: merged headers, mixed date formats, trailing spaces, and month columns spread across 12 headers. Power Query (Data → Get Data) records every cleaning step, so one click refreshes everything when your professor sends updated data.
The 10-Step Cleaning Routine
- Load correctly: Data → From Text/CSV → set File Origin (65001 UTF-8) and Data Type Detection to 'Based on entire dataset'.
- Promote headers: Use First Row as Headers; rename to snake_case (customer_id, order_date).
- Fix types: dates to Date, money to Decimal, IDs to Text (so 00123 keeps zeros).
- Trim & clean: Transform → Trim + Clean on all text columns.
- Split columns: Split 'Name, City' by delimiter; split dates if needed.
- Handle nulls: Replace null emails with 'missing'; filter out test rows.
- Unpivot months: select ID columns → Unpivot Other Columns → rename to Month/Sales.
- Merge lookups: Home → Merge Queries to bring region from a mapping table (Left Outer).
- Remove duplicates: select key columns → Remove Duplicates; keep sort stable.
- Load as Table: Close & Load To → Table + Add to Data Model if building pivots.
Common Exam Traps
- Dates stored as text: use Locale-aware 'Using Locale' type change (English-US vs English-UK).
- Numbers with commas: replace commas only after confirming decimal separator.
- Unpivoted months sort alphabetically: add a Month_Num mapping table and sort by it.
Include screenshots of your Applied Steps in the appendix — professors love reproducible work. If you are short on time, send us the messy file and get back a clean model + documented queries.
