Remove duplicates in Excel without losing data. Power Query will not always keep the top row.

Are Excel's Remove Duplicates and Power Query's the same thing?

No, and this is the whole article in one paragraph. They look alike, they are named alike, and they hand you a different contract. If you only remember one thing, remember which of the two you are standing in when you press the button.

There are four common ways to do this and they differ on more than convenience. Two of them delete rows out of your sheet, and two of them leave your sheet completely alone and write a clean list somewhere else. That second group is the honest answer to "without losing data", because nothing is lost when nothing is deleted.

The case column is the one worth staring at. Power Query is the only one of the four where Microsoft documents a case rule at all, and it is the only one that is case sensitive. So a list that dedupes to 400 rows in the grid can dedupe to 460 rows in Power Query on exactly the same data, and neither number is a bug.

Which row does Excel keep when you remove duplicates?

The first one it meets, reading top to bottom. Microsoft’s help page for filtering for unique values or removing duplicate values says it in one line: when duplicates are removed, the first occurrence of the value in the list is kept, and the other identical values are deleted.

That single sentence is what makes the sort-first habit work here. Sort by date descending and the newest row is now the first occurrence, so the newest row is the one that lives. Sort ascending and you keep the oldest. The sort is doing the real work and the button is just obeying it.

The same page is blunt about the cost. Because you are permanently deleting data, it says, it is a good idea to copy the original range or table to another worksheet or workbook before removing duplicate values. This is not a nervous disclaimer. Remove Duplicates writes over your rows in place, and once the file is saved and closed there is nothing to undo, which is the same reason the cleaning order I use starts by duplicating the tab and naming one copy RAW before a single fix is made.

Why does Power Query keep a different row than the one you sorted to the top?

Because it never agreed to keep that one. Microsoft’s page on working with duplicate values carries a warning that says Power Query does not guarantee that it keeps the first instance in a set of duplicates when it removes duplicates.

The reference page for the underlying function, Table.Distinct, explains why and it is worth reading slowly. Because Power Query sometimes offloads operations to backend data sources, which is called folding, and sometimes optimises a query by skipping operations that are not strictly necessary, there is in general no guarantee which specific duplicate will be preserved. You cannot assume the first row with a unique set of values will remain.

Folding, in normal words, is Power Query deciding not to do the work itself. If your data comes from SQL Server, Power Query can rewrite your steps into a SQL query and let the database do them, which is usually a large speed win. But the database has its own ideas about row order, and it was never told about the sort you clicked in the editor. So your sort and your dedupe can end up being carried out by a different engine in a different order than the one you watched on screen.

Here is the part that explains why almost nobody knows this. Microsoft’s own tutorial page, a few paragraphs under that warning, walks through removing duplicates from a single column and says the output table retains only the first row for each unique value. That is a description of what happened in that example, not a promise about what happens in yours, and it is easy to read it as the second thing.

Which is also why this bug is so hard to catch. The optimiser usually does the obvious thing, so the sorted version returns the row you wanted on Monday, on Tuesday, and for the first eleven months. Then the source changes, or the query starts folding, or a step above it makes the sort skippable, and one month a customer’s oldest order becomes their current record with nothing on screen to say so. That is the same shape as every silent error that reaches a client: it does not throw, it just quietly answers wrong.

How do you make Power Query keep the row you actually want?

You stop treating row order as a rule and make the rule explicit. Microsoft lists three workarounds on its common authoring issues page, under preserving sort, and they are not equal. Two of them are patches and one of them is an actual answer.

1. 01
   Rank on a tie-breaker column, then keep rank 1

   This is the one to use. Order by the columns that hold the duplicates, rank on a tie-breaker such as a modified date or an invoice date, then filter to the rows where rank equals 1. There is a Rank column button on the Add column tab, but check where you are standing before you go looking for it, because Microsoft documents that button as available in Power Query Online only, so it is there in dataflows and Fabric but not in the Power Query that sits inside Excel or Power BI Desktop. On the desktop you get the same rule a different way: Group by on the key column, All rows as the operation, then sort the nested table on your tie-breaker and take its first row. That is the grouping version Microsoft gives on the same page, and either way the rule is now written into the query instead of living in a sort that an optimiser is free to ignore.
2. 02
   Buffer the table before the dedupe

   Table.Buffer loads the table into memory as it currently is, which in many cases makes the following step honour the buffered order. Microsoft words this carefully as in some cases, so treat it as a strong nudge rather than a guarantee, and be aware it blocks folding, so on a big table you pay for it in memory and speed.
3. 03
   Sort after the operation, not before

   If all you need is the finished table in a readable order, do the dedupe first and sort last. This fixes presentation. It does not decide which row survived, so it is not the answer when the surviving row carries a value you care about.
4. 04
   Check the result on a customer you know

   Pick one key that genuinely had several rows, note by hand which row should have won, and look for it in the output. Do this once when you build the query and again the first time the source changes. Thirty seconds, and it is the only step here that catches a problem you did not predict.

The ranking approach costs you about two minutes more than sorting and it turns a habit into a rule. If your dedupe decides which price, which address or which status is the current one, spend the two minutes. If you are only knocking out rows that are identical across every column, sorting was never doing anything for you anyway and Remove duplicates on its own is fine.

Why are there still duplicates in the column after you removed them?

Because the two values are not equal to the software, even though they are obviously the same thing to you. Three causes account for nearly all of it, and all three are documented rather than mysterious.

The first is case. Microsoft’s warning is explicit that Power Query is case sensitive, that it considers the case of the text when working with duplicate values, and that this might lead to undesired results. The recommended fix is in the same warning: apply an uppercase or lowercase transform before you remove duplicates. Do it on a copy of the column if the original spelling matters for the report.

The second is whitespace. A trailing space makes Acme Ltd and Acme Ltd with a space two different values everywhere, in the grid and in Power Query alike. Run Transform then Format then Trim, and Clean while you are there for the invisible control characters that come out of older systems, before any dedupe step.

The third one is the strangest and it is grid-only. Microsoft states that a comparison of duplicate values depends on what appears in the cell, not the underlying value stored in the cell. Their own example is a date: the same date shown as 3/8/2006 in one cell and Mar 8, 2006 in another counts as two unique values. So in the grid you are partly deduping the number formatting, which is why standardising formats belongs before deduping and not after.

There is one more trap that belongs to Power Query specifically. It infers a column’s data type from the first 200 rows only, in Microsoft’s own documentation, so a column that looks numeric at the top and turns into text further down can be typed wrongly. A wrongly typed column compares wrongly, and an incorrect type does not always produce an error, which is what makes it worth checking rather than assuming.

How do you remove duplicates without deleting anything at all?

Do not remove them. Produce a clean list beside the messy one and leave the messy one exactly where it is. This is the reading of "without losing data" that actually holds, and it costs you nothing but a column or a second sheet.

The UNIQUE function is the quickest version if you have it. Type =UNIQUE(A2:A5000) in an empty cell and a clean list spills down from there, live, so it updates when the source updates. Nothing is deleted and you can compare the two lists side by side to see exactly what the dedupe would have taken out. Microsoft also gives it a third argument, exactly_once, which returns only the values that appear precisely one time, and that is a different and often more useful question than which values are unique.

Advanced Filter is the version that works in any Excel, including old ones. Under Data then Sort & Filter then Advanced, choose copy to another location and tick unique records only, and it writes a deduplicated copy wherever you point it. It is a snapshot rather than a live formula, which is sometimes exactly what you want when you are handing a file to somebody.

The third option is my default on anything a client will see, and it deletes nothing either. Add a column that flags duplicates instead of removing them, with =COUNTIFS($A$2:$A$5000,A2)>1 or a rank against your tie-breaker, then filter on that flag. Now the duplicates are visible, countable and reversible, and you can hand somebody a file that says here are the 41 rows I would drop and why, which is a far better conversation than a row count that shrank overnight. If you are wiring this into something that runs every week, that flag column is also the piece that makes an automated Excel report auditable rather than merely fast.

What is the safe order to do this in?

Same order every time, and it takes about five minutes on a normal file. The order matters because each step changes what the next one considers equal.

1. 01
   Copy the sheet and name one RAW

   Right-click the tab, Move or Copy, tick Create a copy, rename it RAW and protect it. Microsoft recommends copying the range before you remove duplicates for exactly this reason. Everything after this happens on the working copy.
2. 02
   Decide what a duplicate is, in words, before you touch a button

   Same customer id is a duplicate. Same customer name is probably not, because two branches share a name. Write the rule down in a cell at the top of the sheet, because that sentence is what you will be defending later if somebody asks why a row is missing.
3. 03
   Clean the key column before comparing it

   Trim, clean, fix the case if the case does not carry meaning, and standardise dates and numbers so the same value looks the same way everywhere. This is the step that makes the dedupe honest, and skipping it is why deduped files still have duplicates in them.
4. 04
   Count the rows now and write the number down

   One number in a cell. Without a before count you cannot tell the difference between removed 3 duplicates and removed 300 rows, and by the time the difference matters the undo stack is gone.
5. 05
   Flag first, delete second, and only if you must

   Add the flag column, filter it, and look at what would go. If the list is what you expected, then remove duplicates in the grid, or filter to rank 1 in Power Query. If it is not what you expected, your key rule was wrong and step two is where you go back to.
6. 06
   Spot-check one key you know by hand

   Pick one customer that had several rows and confirm the surviving row is the one your rule says should survive. This is the check that catches the Power Query ordering problem, and no automated count will catch it for you.

If you are doing this on the same shaped file every month, the whole sequence belongs in a query rather than in your hands, and the choice between building it in Power Query or in a script comes down to who presses refresh and where the output has to land, which is the argument in Power Query or Python first. Either way the rank-and-filter version is the one to encode, because it is the only one that says out loud which row it keeps.