Power Query Intermediate Course: Real-World Data Cleaning in Excel

The beginner course covered the ribbon: split, trim, unpivot, merge and append. Real files are messier. This course handles what breaks most queries at work: hard-coded paths, unpaid-invoice matching, misspelt customer names, “latest record per customer”, junk rows on top of every export, two-row headers and revised invoices, using parameters, custom columns in M, custom functions and Group By with All Rows.

Eight lessons, one zip of realistic messy CSVs, and for each lesson an M solution that Excel ran before publishing, with its actual output.

Before you start: the Power Query Beginner Course. Excel 2016 or later; fuzzy merge needs Microsoft 365 or Excel 2019+.

1. 1Power Query Intermediate Lesson 1: Parameters – One Folder Path for Every QueryStop editing ten queries when the file moves: read the folder path from a named cell or a parameter, and filter with…
2. 2Power Query Intermediate Lesson 2: Merge Kinds – Unpaid Invoices and Orphan PaymentsUse Left Anti and Right Anti merges to find invoices with no payment and payments with no invoice, and know when to…
3. 3Power Query Intermediate Lesson 3: Fuzzy Merge for Misspelt NamesMatch "Sharma Trader", "Mehta and Sons" and "sharma traders" to the right master record with fuzzy merge, and tune the similarity threshold.
4. 4Power Query Intermediate Lesson 4: Group By With All Rows – Latest Record per CustomerKeep the latest credit limit per customer with Group By, All Rows and Table.Max, count changes, and restore column types after expanding…
5. 5Power Query Intermediate Lesson 5: Custom Columns in M – Clean Text, Digits and try … otherwiseWrite custom columns in M: proper-case names, 10-digit Indian mobile numbers, PIN codes and amounts from text like "Rs. 2,250", with try…
6. 6Power Query Intermediate Lesson 6: Custom Functions – Clean Every File the Same WayWrite a custom M function that skips junk header lines, promotes headers and sets types, then apply it to every file in…
7. 7Power Query Intermediate Lesson 7: Two Header Rows – Unpivot a Region-over-Month ReportTurn a report with merged Region headers over Month headers into a clean table: transpose, fill down, combine header rows, unpivot and…
8. 8Power Query Intermediate Lesson 8: Project – ERP Register to HSN-wise GST SummaryFinal project: keep only the latest revision of each invoice, compute GST and build an HSN-wise summary for GSTR-1 style reporting, refreshable…

Next: load these clean tables into a data model in the Power BI course, or automate statements in the Bank Statement course.