Latest data using Group by

Kavya123 — Mon, 15 Mar 2021 02:53:28 GMT

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.

Re: Latest data using Group by

V-lianl-msft — Wed, 17 Mar 2021 05:33:50 GMT

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.

Re: Latest data using Group by

Kavya123 — Wed, 17 Mar 2021 10:29:43 GMT

Thanks for your reply. I got the solution

topic Re: Latest data using Group by in Developer

Latest data using Group by

Re: Latest data using Group by

Re: Latest data using Group by