For each subset in data find latest date

[Simon_]

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 ID	Workpackage	Workpackage finished
P1	A	01.08.2023
P1	B	02.08.2023
P1	C	03.08.2023
P2	A	02.07.2023
P2	B	12.07.2023
P3	A
P3	B	02.08.2023
P3	C	04.08.2023
P3	D

The output I want to generate should look like this:

Project ID	Project finished
P1	03.08.2023
P2	12.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

[ThxAlot]

finished.pbix

Way more concise to use DAX

[Manoj_Nair]

@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"

[Vijay_A_Verma]

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"