Power Query: Identifying duplicate unique values and retaining the latest entry based on date and time

Question

In a table visual, the requirement is:

Unique ID	Date	Total
1002	5/15/23 6:34 AM	9.8
1007	5/15/23 6:28 AM	6.7
1003	5/15/23 4:01 AM	3.2
1009	5/15/23 3:16 AM	4.4
1009	5/15/23 2:39 AM	4.5
1001	5/15/23 12:59 AM	9.9
1005	5/15/23 12:41 AM	10
1004	5/15/23 12:29 AM	7.8
1002	5/15/23 12:08 AM	6.6
1009	5/17/23 5:40 PM	7

The objective: I'm trying to identify the duplicate values in Column A (Unique ID), and retain the latest entry by Column B (Date).

The anticipated end result would look something like this:

Unique ID	Date	Total
1002	5/15/23 6:34 AM	9.8
1007	5/15/23 6:28 AM	6.7
1003	5/15/23 4:01 AM	3.2
1009	5/15/23 3:16 AM	4.4
1001	5/15/23 12:59 AM	9.9
1005	5/15/23 12:41 AM	10
1004	5/15/23 12:29 AM	7.8

Any help is greatly appreciated.

I have tried my luck with a few conditional formulas, but to no avail.

Answer 1

You can try this in M/powerquery (note sample output in question is wrong)

Right click Unique ID, group by, using operation all rows

In formula bar remove everything betweeen and including the [] brackets

Sort the data table, so _ becomes Table.Sort(_,{{"Date", Order.Descending}})

Take the first row of that (now the largest date) so it becomes Table.FirstN(Table.Sort(_,{{"Date", Order.Descending}}),1)

Click the arrow atop the new column and expand it

~~

Total sample code, you can paste into home...advanced editor..

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Unique ID"}, {{"data", each Table.FirstN(Table.Sort(_,{{"Date", Order.Descending}}),1), type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Date", "Total"}, {"Date", "Total"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded data",{{"Date", type date}})
in  #"Changed Type"