Removing duplicates using running index number?

[M0]

Hi, 

I'm trying to keep the SKU with the oldest date and remove all other rows.

In Excel, I could've done it in this way as in the last column Formula.  I then keep the rows with the first occurrence of the SKU.

Can I replicate the =COUNTIF(A2,$A$2:A2) formula in PQ?

If not what else should I do to keep the right row?

At first I thought this would be easy to solve using the Remove Duplicates in PQ.  To my surprise this does not work as it does in Excel.  In PQ, it does not automatically save the first occurrence and remove the others (like in Excel).  Why is this different?

I think this is especially true when I have a large table. I still don't understand why.

[M0]

Thank you very much Pat!

I didn't use this exact solution, but it did help me to get around this problem, which is what I wanted 🙂

[ppm1 · Accepted solution]

Here's one way to do it in the query editor, with a GroupBy and the Table.Max function.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUTLUN9I3MjAyBjIdlWJ1kMQNYeJOqOLGMHFnmLgRkGOEUI8iboxDHMl8oL2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, #"Delivery Date" = _t, Other = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", Int64.Type}, {"Delivery Date", type date}, {"Other", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SKU"}, {{"AllRows", each _, type table [SKU=nullable number, Delivery Date=nullable date, Other=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.Max(_, "Delivery Date")}}),
    #"Expanded AllRows" = Table.ExpandRecordColumn(Custom1, "AllRows", {"Delivery Date", "Other"}, {"Delivery Date", "Other"})
in
    #"Expanded AllRows"

Pat