Get the Power Query Workbook
Grab the ready-to-use Excel file from the video. See the bug run live, then fix it with one line: Table.Buffer. CSV included.
To keep the latest record per group in Power Query, most people sort newest-first and then remove duplicates to keep the first row. On larger datasets this can keep an older row instead, because Power Query uses lazy evaluation and does not guarantee your sort order survives before duplicates are removed. Microsoft confirms the surviving duplicate is not guaranteed. The fix: wrap the sort step in Table.Buffer() to lock the order in memory, or use Group By to return the latest timestamp per group, which never depends on sort order at all.
The Goal: Keep the Latest Record Per Group
The task is common: from a file with repeated entries, keep the most recent record for each group. One row per product per warehouse, so operations knows what’s on the shelf right now.
Picture a CSV straight out of your warehouse system. Every time someone counts inventory, it logs a new row: record ID, product, warehouse, timestamp, movement type, quantity. The same product turns up again and again, even in the same warehouse, each row with its own timestamp.
You want the latest count for each one. That’s the whole job, and it’s the same shape as keeping the latest invoice per customer or the newest status per ID.
(New to all this? Start with what Power Query is and why it’s worth learning.)
The Logical Approach That Quietly Fails
The obvious method to keep the newest row: sort by timestamp newest-first, then remove duplicates on Product and Warehouse so Power Query keeps the first row of each. That first row should be the latest.
Here’s the drill. Data > Get & Transform Data > From Text/CSV, pick the file, then Transform Data. Power Query promotes the headers and sets your types automatically.
Now sort. Click the Timestamp dropdown and Sort Descending, so the newest stamp sits on top. Then select Product, hold Ctrl, click Warehouse, right-click, and Remove Duplicates. Power Query keeps the first instance of each combination. With a newest-first sort, that should be the latest record.
Trim the rest. Choose Columns to drop Record ID and Movement Type, sort by Warehouse then Product, then Close & Load.
It looks perfect. Bolt Running Shoes Red in the AMS-01 warehouse shows a newest timestamp of 11 January 2025 and a quantity of 49.
Proof: The Query Kept the Wrong Record
Check it against the source and it falls apart. The real latest record is 8, stamped February 2026. The query reported 49 from January 2025.
Open the same CSV in Excel and sort it properly: Warehouse, then Product, then Timestamp oldest-to-newest. Scroll to Bolt Running Shoes Red, AMS-01. The most recent row is February 2026, quantity 8.
So Power Query kept an older January 2025 row. And the step list plainly shows Removed Duplicates sitting right after a descending sort. The order looks right. The record is wrong.
Why Power Query Keeps the Wrong Record
Power Query doesn’t always run your steps in the order you see. It uses lazy evaluation, and on a large table it does not guarantee your sort order is preserved before duplicates are removed. So “keep the first row” no longer means “keep the latest record.”
Lazy evaluation is Power Query being efficient. It streams rows and avoids work it thinks it can skip, and fully materializing a sort on a big table is expensive. So the sorted order you set up isn’t guaranteed to reach the Remove Duplicates step intact.
This isn’t a secret. Microsoft says it in their own documentation: when you remove duplicates, there’s no guarantee which instance is kept (Microsoft Learn).
Now the dangerous part. On a small file, everything fits in memory, the sort holds, and you get the right answer. Move the same query to a large file or a production refresh, and it returns the wrong record. No. Not a crash. Wrong data that looks completely right.
It’s one of several Power Query behaviors that catch people off guard.
The One-Line Fix: Table.Buffer
To keep the latest record reliably, wrap your sort step in Table.Buffer(). It loads the sorted table into memory as a fixed snapshot, so the order is locked before Remove Duplicates runs.
Turn on the formula bar first if you can’t see it: View tab, tick Formula Bar. Click the Sorted Rows step, put your cursor right after the =, type Table.Buffer(, jump to the end of the line, close the bracket, and press Enter.
Your step goes from this:
#"Sorted Rows" = Table.Sort(#"Changed Type", {{"Timestamp", Order.Descending}}),to this:
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type", {{"Timestamp", Order.Descending}})),
Send it back to Excel. Bolt Running Shoes Red in Amsterdam now reads 8, matching the source.
Sorting is expensive, which is why Power Query tries to avoid committing to it. Table.Buffer forces the issue: fully sorted, fully in memory, before anything downstream touches it.
The Master Excel Power Query course goes deep on the M language if you want to understand why steps behave this way.
Featured Course
Master Excel Power Query – Beginner to Pro
Full corrected query:
let
Source = Csv.Document(File.Contents("C:\Data\warehouse_stock_counts.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Record ID", Int64.Type}, {"Product", type text}, {"Warehouse", type text}, {"Timestamp", type datetime}, {"Movement Type", type text}, {"Quantity", Int64.Type}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"Timestamp", Order.Descending}})),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Product", "Warehouse"}),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates",{"Product", "Warehouse", "Timestamp", "Quantity"}),
#"Sorted Rows1" = Table.Sort(#"Removed Other Columns",{{"Warehouse", Order.Ascending}, {"Product", Order.Ascending}})
in
#"Sorted Rows1"Heads up on big data. Table.Buffer pulls the whole table into memory. On a few thousand rows that’s nothing. On millions, the memory cost can slow your refresh or cancel out the benefit (reference). For very large data, use the Group By method below.
The Sturdier Fix: Group By to Keep the Latest Record
A second method keeps the latest record without depending on sort order at all: Group By the columns that define your group, then pull the row with the latest timestamp from each. This is the safer default on large data.
Here’s the recipe, no buffering required:
- Select Product and Warehouse, then Home > Group By.
- For the operation, choose All Rows. This nests every row for each product-and-warehouse combination into its own mini table.
- Add a custom column that grabs the latest row from each group:
Table.Max([AllRows], "Timestamp")That returns the single record with the newest timestamp.
- Expand that record to pull out Timestamp and Quantity.
Because Group By picks the row by its timestamp value, not its position, it doesn’t depend on sort order at all. That’s what makes it the safer choice on large data.
| Approach | Best for | Watch out |
|---|---|---|
Table.Buffer | A fast fix on a query you’ve already sorted; small to mid-size data | Pulls the full table into memory, slow on millions of rows |
| Group By (latest per group) | Large data, production pipelines, anything refreshed often | A few more clicks to set up |
How to Check If Your Existing Queries Are Affected
If you’ve ever sorted and then removed duplicates to keep the latest record, check it now. The risk is real anytime you rely on sort order to pick a “first” or “latest” row.
Three quick tells:
- You sort, then Remove Duplicates to keep one row per key. That’s the exact pattern that breaks.
- The result looked right when you built it on a sample, then the data grew. Small data hides this. Production data exposes it.
- No
Table.Bufferon the sort step, and no Group By. If neither safeguard is in place, the order isn’t guaranteed.
The fix takes ten seconds: open the query, find the sort step, wrap it in Table.Buffer(), and re-check one record you can verify by hand. If the number changes, you just caught a reporting error before it caught you.
Key Takeaways
- To keep the latest record per group, sorting and then removing duplicates can keep an older row on large data.
- The cause is lazy evaluation: the sort order isn’t guaranteed to survive to the Remove Duplicates step. Microsoft doesn’t guarantee which duplicate is kept.
- It fails silently. No error, no broken refresh, just the wrong record. Fix it by wrapping the sort in
Table.Buffer(), or use Group By to pull the latest row per group. - For very large data, prefer Group By. Buffering forces the whole table into memory.
Frequently Asked Questions
How do I keep the latest record for each group in Power Query?
Sort newest-first, then either wrap the sort in Table.Buffer() before removing duplicates, or use Group By to return the row with the maximum timestamp per group. The Group By method is safer on large data because it doesn’t depend on sort order.
Why does Power Query keep the wrong record when I remove duplicates?
Lazy evaluation. On large data, Power Query doesn’t guarantee your sort order is preserved before it de-duplicates, and Microsoft doesn’t guarantee which duplicate survives. Wrap the sort in Table.Buffer, or use Group By.
Does Remove Duplicates keep the first or the latest row?
It keeps the first row it meets during evaluation. That isn’t guaranteed to be the first row you see after sorting, which is why “keep the latest record” can quietly return an older one on large tables.
How do I keep the most recent record instead of the oldest?
Sort by your date or timestamp column descending so the newest is on top, then remove duplicates. To make it reliable, buffer the sort step or use Group By with the maximum timestamp.
What does Table.Buffer actually do?
It reads the table into memory as a fixed snapshot at that step, so later steps can’t re-order or re-evaluate it. That locks your sort in place before Remove Duplicates runs.
Will this happen in Power BI too?
Yes. Same M engine, same behavior. The fix is identical.
My result looks correct on a small file. Am I safe?
Not necessarily. Small tables often fit in memory, so the sort holds. The problem tends to appear once the data grows or moves to production. Always test on full-size data.
Get the Practice Workbook
The exact file from the video. Break the query, watch it return the wrong record, then fix it in one line with Table.Buffer. CSV included.
Take It Further
This is one trap among many. Query folding, privacy levels, M-language quirks: they all behave like this one, fine until they aren’t.
The full Master Excel Power Query course takes you from beginner to pro-level techniques like the Table.Buffer fix and the Group By method, on real-world data the whole way.
Featured Course
Master Excel Power Query – Beginner to Pro
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
