Group by Max date without losing other bits of data

[faysanderson]

Using this table...

Client ID	Assessment ID	Assess Date
123	101	10/1/24
123	102	1/1/24
234	300	16/3/24
234	302	20/6/24
345	405	6/9/24
345	410	10/9/24

I want to group by Client ID number to show the Max Assess Date. When I use the UI to do this, the code comes out as...

= Table.Group(#"Changed Type", {"Client ID"}, {{"Max Assess Date", each List.Max([Assess Date]), type nullable datetime}})

and in the result, I lose the Assessment ID that corresponds to the Max Assess Date.  Result looks like this...

Client ID	Max Assess Date
123	10/1/24
234	20/6/24
345	10/9/24

But what I want is to keep the Assessment ID that corresponds to the Max Assess Date...

Client ID	Assessment ID	Max Assess Date
123	101	10/1/24
234	302	20/6/24
345	410	10/9/24

How do I change the code to do this?  Or is it possible to do through the UI boxes?

[ThxAlot]

[slorin]

Hi @faysanderson 

= Table.Group(#"Changed Type", {"Client ID"}, {{"Max Assess Date", each Table.Max(_, "Assess Date"), type record}})

 Expand "Assessment ID" and "Max Assess Date"

Stéphane

[shafiz_p · Accepted solution]

Hi @faysanderson  Try below code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc1BCsAwCETRu7gOOI5W6FlC7n+NxpSSQjZ/MQ+xdzG6NDHYqpoSDBltC6t/oMecHChI9VPqhNDc4nHNLVBNvQ8wvP8/GQ8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client ID" = _t, #"Assessment ID" = _t, #"Assess Date" = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client ID", Int64.Type}, {"Assessment ID", Int64.Type}, {"Assess Date", type text}}),

    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Assess Date", type date}}, "en-GB"),

    #"Grouped Rows" = Table.Group( 
             #"Changed Type with Locale",
             {"Client ID"},
             {
                {"Max Assess Date", each List.Max([Assess Date]), type nullable date},
                {"Assessment ID", each 
                    let
                        maxDate = List.Max([Assess Date]),
                        maxRow = Table.SelectRows(_, each [Assess Date] = maxDate)
                    in
                    maxRow{0}[Assessment ID], 
                type nullable number}
            }
)
in
    #"Grouped Rows"

Desired output:

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[faysanderson]

Thank you.  I haven't completely wrapped my head round why this works or how to implement it in other scenarios, but it has solved my issue. 😀