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 is considered finished when all work packages are finished, the finished date is the latest date of the work packages. Different projects have varying numbers of work packages.

The input looks like this:

Project IDWorkpackageWorkpackage finished
P1A01.08.2023
P1B02.08.2023
P1C03.08.2023
P2A02.07.2023
P2B12.07.2023
P3A 
P3B02.08.2023
P3C04.08.2023
P3D 

The output I want to generate should look like this:

Project IDProject finished
P103.08.2023
P212.07.2023
P3 

 

So the logic is: For each Project ID, check if all work packages are finished, if yes find and return latest date, else return null.

 

I would really really appreciate any help. I think I could code this in C but I'm totally lost in Power Query M.

Thank you!

Simon

Need Help

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    Sample code below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lFyBGIjAyNjXQMLXQNDpVgdqLgTsrgRQtwZWdwYIm6EYo45XL0RijnmuoZQcWOoegQPm23G6LaZIMRdwLpjAQ==", 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"}, {{"Project finished", each if List.Contains(_[Workpackage finished],null) then null else List.Max(_[Workpackage finished])}})[[Project ID], [Project finished]]
in
    #"Grouped Rows"
  • 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"