📎 This article includes 3 downloadable practice files ↓
History tables (price changes, credit limits, employee grades) have several rows per key. Reports usually need only the latest. Removing duplicates keeps the first row it sees, which is not the latest, so use Group By.
Grouped = Table.Group(Source, {"Customer"},
{{"Latest", each Table.Max(_, "Changed On"), type record},
{"Changes", each Table.RowCount(_), Int64.Type}}),
Expanded = Table.ExpandRecordColumn(Grouped, "Latest", {"Changed On", "Credit Limit"}),
Typed = Table.TransformColumnTypes(Expanded, {{"Changed On", type date}, {"Credit Limit", Int64.Type}})_ inside the group is the sub-table of that customer’s rows; Table.Max(_, "Changed On") returns the whole row with the highest date.
Table.Max([All], "Changed On"). Same result.Practice
Unzip the source files (e.g. to D:\PQ\). Try to build the query with the ribbon first, then compare with the M solution and the expected result. Every solution was run by Excel’s own Power Query engine before publishing.
📎 Practice files for this article
⬇ Download all 3 files (ZIP · 8 KB)
- 🗂️Practice source files (zip)All messy CSVs for the course: invoices, payments, typed customer names, credit history, contacts, branch files, a two-row-header report and an ERP register.⬇ ZIP · 3 KB
- 📄Lesson 4 M solutionThe full query. Paste into Advanced Editor and change the Folder line.⬇ PQ · 955 B
- 📗Expected resultWhat the query returned when Excel ran it, to compare with yours.⬇ XLSX · 5 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.