I have a table of data which I have sorted by an ID code (ascending, but that's immaterial), then by a date (descending - that's important). All I want to keep is the latest (most recent) dated record for each ID code.
I'm pretty sure that Excel's "Remove Duplicates" always treats anything after the first as a duplicate (I'd appreciate your confirmation of that, as an aside).
It looks as if "Table.Distinct" doesn't act the same way, at least not consistently. Can you tell me how this function "acts"? By adding an index column, I can get Table.Distinct to act the way I want it to, but I'm interested in knowing how it actually behaves.
Thanks!
Craig
Hi Craig,
Looking at the official documentation it looks like first keeps the first row (although this isn't explicitly said), removing duplicates of that row that follow - see Example 2
Table.Distinct - PowerQuery M | Microsoft Docs
However I have seen some PQ functions do unexpected things. If you are getting unexpected results try wrapping the table in Table.Buffer e.g.
Table.Distinct(Table.Buffer(MyTable))
Regards
Phil
