Forum Discussion

Simon_'s avatar
Simon_
New Member
3 years ago

For each subset in data find latest date

Hi folks, I want to analyse a table with Power Query of dates of work packages for different projects. I need to create a new table containing a list of all projects and their finish date. A project...
Need Help
Manoj_Nair's avatar
Manoj_Nair
Solution Supplier
3 years ago

Simon_- Please check this out, copy the M code in your advanced editor, use the same field name. Let me know if this works. If this fix your problem, please tick this a solution and a thumps up.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lFyBGIDQz0DCz0jAyNjpVgdqLgTSNwIU9wZJG6MKm4EMweo3hxNHGSOIZq4MVS9AoKLzTpjmHUmmOIuQKwUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, Workpackage = _t, #"Workpackage finished" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", type text}, {"Workpackage", type text}, {"Workpackage finished", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project ID"}, {{"MaxDate", each List.Max([Workpackage finished]), type nullable date}})
in
    #"Grouped Rows"