Power Query masterclass: Excel learning resource
Power Query masterclass
A long-form (90-minute) course rather than a quick-tips video, structured around four complete worked examples instead of isolated tricks.
What this video actually covers
A long-form (90-minute) course rather than a quick-tips video, structured around four complete worked examples instead of isolated tricks. The employee-data example is the closest match to typical messy-workbook cleanup: importing a raw employee list, fixing data types, and building summary analysis on top of it inside Power Query instead of with worksheet formulas. The later sections move outward from cleaning one file to broader automation — pulling data from a live web page, combining every file sitting in one folder into a single table, and merging/appending multiple datasets together (the SQL-style join equivalent inside Excel). Given the length, this is better used as a reference to jump into one specific example than to watch start to finish.
Where to jump in
What to practice while watching
- Load the messy-data practice file into Power Query and follow the employee-data workflow: fix data types, clean up inconsistent values, and build a summary table from the cleaned query.
- Save two or three copies of the practice file into one folder and combine them into a single query, the way the folder-consolidation example does.
- Create a second small lookup table and use Power Query's Merge Queries — not VLOOKUP — to bring a column across from it.
Exercise file
Practice the steps above on a real workbook instead of a blank sheet — no sign-up required.
Download Messy data cleanup practice (.xlsx)Recommended learning path
Start by watching the lesson once without pausing, then reopen Excel and rebuild the example with your own small dataset. Save one clean practice workbook before moving to the next topic.
Source details
- Channel: Chandoo
- Format: video
- Level: Intermediate
- Topic: Data Cleaning
Go deeper with this skill
Turn messy imported data into a reliable table that formulas, pivots, and reports can use safely. For this article, the goal is to practice: A long-form (90-minute) course rather than a quick-tips video, structured around four complete worked examples instead of isolated tricks.
Practice workbook setup
Copy messy sample data to a raw-data sheet and keep the original untouched while you clean a separate working copy.
Practice workflow
- Identify the expected columns and data types before changing anything.
- Remove obvious blanks and duplicates, then check whether any removals need business approval.
- Standardize spacing, casing, dates, and number formats.
- Create a small before-and-after table so the cleanup can be reviewed.
Quality checks
- The row count before and after cleanup is explainable.
- Key columns have no unexpected blanks.
- Dates, IDs, and amounts are stored as the correct data type.
Common mistakes
- Cleaning directly over the only copy of the data.
- Removing duplicates without deciding which columns define a duplicate.
- Fixing visible rows while hidden filtered rows stay messy.
Next actions
- Convert the cleaned range into an Excel Table.
- Repeat the same cleanup with Power Query if the data will arrive again.
Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.
