Keep Earliest record of ID in Power Query

[ExcelAtEverything]

Hello!
I'm looking for some code to plugin to the query editor which will allow me to keep only the earliest date for each Item code / Shipment ID combination. Here's what I mean exactly.

• The middle column shows the item code for each item sold.
• The left column shows the shipment ID# for the delivery package of that actual unit when it was delivered. showing a unique shipment ID for each time it was delivered.
• The right column are sale dates for each time an item was sold.

For each & every item code, I need to find which shipment ID it came in on. Then find the earliest date within those constraints & keep that row, while filtering out the others with later sale dates. Then move on to the next item code. There will be identical item codes but with different shipment IDs, because they were received at a different time & therefore are part of a different batch.

This will be the very last line of code before my "in". Here is the 1st part of the line where I want to insert the code after:

 #"Renamed Columns4" = Table.RenameColumns(#"Extracted Last Characters",

 

[JGordon11]

try

    tbl1 = Table.Group(#"Renamed Columns4", {"Item Code"}, {{"All", each Table.SelectRows(_, (x)=> x[Date] = List.Min(_[Date]))}}),
    tbl2 = Table.ExpandTableColumn(tbl1, "All", {"Shipment ID", "Date"}, {"Shipment ID", "Date"}),
    Result = Table.ReorderColumns(tbl2,{"Shipment ID", "Item Code", "Date"})

 

[alansidman]

If all you need is the earliest date, then group by the item code and select Min for the Date.
If you need the shipment ID, then you will need to dupe the original query and join (merge) it with original on the Item Code.

If you need explicit Mcode, then upload a representative sample of 15-20 records using XL2BB. If unfamiliar with this function, look at my signature. We cannot manipulate data in a picture and your current picture does not provide enough data with different dates and shipment varieties

 

[ExcelAtEverything]

Thanks Alan!