Remove all duplicate except first?

Mr_Mr · ‎11-27-2023

I've been banging my head on my desk/kitchen table as this should work but for some reason is eluding me.

I have two columns, first is a list of IDs and the second is a date field. Is there a way that I can sort the columns and then remove all the duplicate IDs but it keeps the most recent? So only one ID remains with the most recent date adjacent to it?

I have tried several ways but it keeps removing the wrong IDs.

Thank you in advance.

Ahmedx · ‎11-27-2023

to know how to do this watch my video

https://1drv.ms/v/s!AiUZ0Ws7G26RjAmkzSoR5Dhll_Bq?e=Nw39fj

Ahmedx · ‎11-27-2023

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Ahmedx · ‎11-27-2023

you can do it in power query:
1) grouping by ID
2) leave the lines where the maximum date is indicated

ERD · ‎11-27-2023

@Mr_Mr , it would be better if you provide some small demo table with the desired result.

How to Get Your Question Answered Quickly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!