Remove duplicates from collection but keep latest row?

[david.jeffery]

In the following collection, I need to remove duplicates but keep the latest row. 

For example:

• Keep item ID 37304873 with the completed date of 25/01/2024 but remove the row containing the same ID that has an exception date of 24/01/2024.
• Keep item ID 21116344 with the exception date of 29/01/2024 but remove the row containing the same ID that has an exception date of 24/01/2024.

I'm installed a BO that will remove the duplicates, but it doesn't always keep the latest row, even after sorting. 

Any advice would be very much appreciated.

[EmersonF]

Hello @david.jeffery, I believe that the better option is, create a collection contains "Item Ids" with unique values and filter the colunms like 

"[Item ID] = '" &[Data Item with Item ID]&" ' AND ([Exception] = MAX(Exception]) OR  ([Completed] = MAX(Completed]))"

After that you can append the filter result in another final collection.

My collection

My filter 

My output

[david.jeffery]

That looks great @EmersonF , if possible could you expand a little further on the exact steps you're taking to achieve this?

[EmersonF]

Of course, my friend.
So, let by step...
1º Remove duplicate values ​​only from "Item ID"

2º With a collection with only unique ID items, run a loop

3º Inside the loop there must be the Filter action containing the filter:
"[Item ID] = '" &[Collection Generated in step 3.Item ID]&" ' AND ([Exception] = MAX(Exception]) OR  ([Completed] = MAX(Completed]))" The result of this filter , will result in just one more current line.

4º Still within the loop below the filter, add an action that will insert the filter result into a final collection of your choice.

5º After performing all the steps, you will have a final collection with all the unique and most recent items.

[david.jeffery]

Thank you Emerson. I've set that up already but I see this when I run the filter:

[EmersonF]

Can you show me the full screenshot with filter?

[david.jeffery]

[Queue Data] is the original collection containing duplicates.

[Unique Item ID's.Item ID] is the collection containing the unique item ID's

[david.jeffery]

Fixed some issues with the formatting. It kind of works but only if there are duplicate item ID's, if the item id isn't duplicated I'm not seeing any results

[EmersonF]

Sorry for my delay, in this case, you can make three filters by ID, exceptions and completed and validate the result

1 filter "[Item ID] = '" &[Collection Generated in step 3.Item ID]&" '  "

2 Filter: "[Exception] = MAX(Exception])"

3 Filter: 2 Filter: "[completed] = MAX(completed])"

If has result in filter 2 or 3 you get the result e put in the final collection