1

When running Remove Duplicates in Power Query, does it leave the 1st instance alone and then delete any following duplicates? E.G. If there were duplicates on rows 10,11 and 12, it would delete rows 10 & 11? Is there documentation on this somewhere?

Thanks!

Diet_Rich
  • 15
  • 2
  • 6

2 Answers2

2

As far as I am aware, remove duplicates will remove items based on the order the data was initially loaded into Power Query. Any sorting or other operations you have performed after the data is loaded will not be factored into this. So duplicate items on rows 11 and 12 would be removed in your example, even if you sorted the data so the items on rows 11 and 12 were now above the item on row 10.

It is possible to make remove duplicates follow the current sort order if you use the function Table.Buffer() on the data before using the remove duplicates function in PQ (the actual function it runs is Table.Distinct(). This is because Table.Buffer() loads the table at the current state it is called into memory, and this resets the "load" order that is used to remove duplicates by Table.Distinct.

In practice the simplest way to do it looks like changing the default function when you use Remove Duplicates from this

= Table.Distinct(#"Sorted Rows", {"DuplicateColumn"})

to this

= Table.Distinct(Table.Buffer(#"Sorted Rows"), {"DuplicateColumn"})
Wedge
  • 1,766
  • 1
  • 8
  • 14
  • I would lead with your second paragraph. It is too confusing to read that after the first one. – teylyn Jan 12 '20 at 19:59
0

Not sure about documentation but from experience: Yes, the first item is retained, any duplicates following will be removed.

With this knowledge under your belt you can use an Index columns to manipulate the order of entries if the default order does not produce the result you want.

teylyn
  • 34,374
  • 4
  • 53
  • 73