Add custom column by finding the latest date for each "key" column

[Jigar1276]

Hi,

I want to add one column to following table where I can get flag about latest "Status Change Date" for each "key".

For bellow screenshot, I want "Yes" for key "st-273" for blue highlated date row. Simillary for key "st-272" lastest date is yellow highlated row.

Thanks in advance for any help in this regards.

[Jakinta · Accepted solution]

Hope this can help.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc4xCoAwEETRq8jWgcmsCXHt9Boh97+GhlTiwFSvGH7vdlmywA7nls88ZyMtbuChnHDpFe7KCxjKXfi9/Pc/nURR/sY35YH64fEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [key = _t, #"Status Change Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Status Change Date", type datetime}}),
    GroupedRows = Table.Group(ChangedType, {"key"}, {{"MaxDate", each List.Max([Status Change Date]), type nullable datetime}}),
    #"Added Custom" = Table.AddColumn(ChangedType, "Custom", each if let k=[key] in [Status Change Date] = Table.SelectRows(GroupedRows, each ([key]=k )){0}[MaxDate] then "Yes" else "")
in
    #"Added Custom"

[Jigar1276]

@Jakinta This is perfect. Thanks a lot for this. 👍