Latest data using Group by

[Kavya123]

Hi All,

I need to have a new desc column based on  the latest data. 

For ex: If ID is 1363 then new Desc is CC ( Max date 1/20/2021 9:11:12 PM)

Please help me to achieve this.  Please find the sample data and resultant output.

[V-lianl-msft · Accepted solution]

Hi @Kavya123 ,

Please try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2M1bSUXJ0dASSRgZGBrqWusYGSrE6cCknJyeIlKEuEFoiSzk7I2SMUDS5uLjAzLPQNTJDlnJzc8NlnqurK6qBsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, Desc = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Desc", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"Max", each List.Max([Date]), type nullable date}, {"S", each _, type table [id=nullable number, Desc=nullable text, Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectRows(
     [S],
     (itable) => itable[Date]=[Max])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Desc"}, {"Desc"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"S"})
in
    #"Removed Columns"

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kavya123]

Thanks for your reply. I got the solution