Group Rows to select rows with max date

Power Query
1

Hi @Melissa

Please find attached excel file. I have already grouped the rows and I just need to be able to select the rows here with the max date.

image
image1928×987 93.8 KB

Thanks

Patrick N
Group Rows to select row with max date.xlsx (19.3 KB)

2

Hi @ambepat,

You are grouping on both [Employee #] and [WeekEnding] so you’ll end up with duplicate Employee IDs. Even with filtering on Max [Date] I noticed tables with multiple rows - however when that’s the case I’ve kept the last record from the nested table.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    GroupRows = Table.Group(PromoteHeaders, {"Employee #", "WeekEnding"}, {{"AllRows", (OT) => Table.LastN( Table.SelectRows( OT, (IT) => List.Max(OT[Dates]) = IT[Dates]), 1), type table [Dates=datetime, #"Employee #"=number, WeekEnding=datetime, Status1=text]}}),
    ExpandRows = Table.ExpandTableColumn(GroupRows, "AllRows", {"Dates", "Status1"}, {"Dates", "Status1"})
in
    ExpandRows

.
I hope this is helpful.

1 Like
3

@Melissa. Thanks will have a look at the solution tomorrow. Appreciate it.