Add column based of calculation of multiple rows

Hello, hope this helps. 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRMgTilPy8VKVYHUyJzLz4gqL89KLU4mIMeSMgLslPyccqgWGiEy6rUCViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Milestone = _t, #"Task Status" = _t]),
#"Grouped Rows" = Table.Group(Source, {"Project Name"}, {{"Count", each _, type table [Project Name=nullable text, Milestone=nullable text, Task Status=nullable text]}}),
Add_Column = (check)=>
let
#"Task Status" = check[Task Status],
Custom1 = List.ContainsAll({"done"},#"Task Status")
in
Custom1,
#"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "Milestone reached", each Add_Column([Count])),
#"Expanded Count" = Table.ExpandTableColumn(#"Invoked Custom Function", "Count", {"Milestone", "Task Status"}, {"Milestone", "Task Status"})
in
#"Expanded Count"

Hi RobotBroer ,

How about this one:

The idea is the following:

1. Filter the table where task status <> done

2. Get a distinct list of all Project Names left

3. Join the initial table with the distinct list

4. Anytime you get a hit --> your Project is not yet finished

I am not sure whether this is exactly what you want since your example table indicates that a milestone for a project can be both done and in_progress (milestone 1 for project A). So I might have misunderstood your query. However, I believe the idea from above should work for other scenarios as well. Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRMgTilPy8VKVYHUyJzLz4gqL89KLU4mIMeSMgLslPyccqgWGiEy6rUCViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Milestone = _t, #"Task Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Milestone", Int64.Type}, {"Task Status", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Task Status] <> "done")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Project Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Project Name"}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Project Name"}, #"Removed Duplicates", {"Project Name"}, "Removed Duplicates", JoinKind.LeftOuter),
    #"Expanded Removed Duplicates" = Table.ExpandTableColumn(#"Merged Queries", "Removed Duplicates", {"Project Name"}, {"Removed Duplicates.Project Name"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Removed Duplicates", "Custom", each if [Removed Duplicates.Project Name] <> null then false else true),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Removed Duplicates.Project Name"})
in
    #"Removed Columns"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Thank you,

I just had to add another dimension for the milestone too:

MILESTONE REACHED =
IF (
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Project Name] = EARLIER ( 'Table'[Project Name] )
             && 'Table'[Milestone] = EARLIER ( 'Table'[Milestone] )
                && 'Table'[Task Status] <> "done"
        )
    ) > 0,
    "FALSE",
    "TRUE"
)