How do I add a filter to a Table.AggregateTableColumn

 You want to keep all 140 rows and add a new column that counts the “Done” items, but only for those rows where the status is “Done”. For the other rows, the new column should have null values.

Here’s how you can achieve this:

1. Create a filtered table for “Done” items.
2. Aggregate the filtered table.
3. Merge the aggregated results back to the original table.

Here’s the revised code:

let
// Step 1: Create the initial merged table
#"Merged Queries1" = Table.NestedJoin(#"Expanded AgileTeams", {"Parent"}, #"Expanded AgileTeams", {"Parent"}, "Expanded AgileTeams", JoinKind.LeftOuter),

// Step 2: Aggregate the initial merged table
#"Aggregated Expanded AgileTeams" = Table.AggregateTableColumn(#"Merged Queries1", "Expanded AgileTeams", {{"Parent", List.Count, "Count of Expanded AgileTeams.Parent"}}),

// Step 3: Filter the table for "Done" items
#"Filtered Done Rows" = Table.SelectRows(#"Aggregated Expanded AgileTeams", each [Status] = "Done"),

// Step 4: Aggregate the filtered table
#"Aggregated Filtered Rows" = Table.AggregateTableColumn(#"Filtered Done Rows", "Expanded AgileTeams", {{"Parent", List.Count, "Count of Done"}}),

// Step 5: Merge the aggregated "Done" results back to the original aggregated table
#"Merged Queries2" = Table.NestedJoin(#"Aggregated Expanded AgileTeams", {"Parent"}, #"Aggregated Filtered Rows", {"Parent"}, "Aggregated Filtered Rows", JoinKind.LeftOuter),

// Step 6: Expand the merged table to include the new column
#"Expanded Merged Queries2" = Table.ExpandTableColumn(#"Merged Queries2", "Aggregated Filtered Rows", {"Count of Done"}, {"Count of Done"}),

// Step 7: Rename the columns as needed
#"Renamed Columns3" = Table.RenameColumns(#"Expanded Merged Queries2", {{"Count of Expanded AgileTeams.Parent", "SolutionEpicChildCount"}})
in
#"Renamed Columns3"

This approach ensures that all 140 rows are retained, and the new column “Count of Done” will have values only for the rows where the status is “Done”. For other rows, it will be null.