daxpowerquerycustomcolumn

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

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.

Solution

  • 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"

    enter image description here