Forum Discussion
How do I add a filter to a Table.AggregateTableColumn
hi,
I have the following, which is working as expected:
= Table.AggregateTableColumn(#"Merged Queries1", "Expanded AgileTeams", {{"Parent", List.Count, "Count of Expanded AgileTeams.Parent"}})
What I want to do this again as a seperate column, but add a filter condition such as "where status = 'Done'" (this is SQL syntax, so not sure of Power M syntax to do this.
- Done Employee Count =var curParent = [Parent]
var final =
COUNTROWS(FILTER('Check Table', [Parent] = curParent && 'Check Table'[Status] = "Done" && [Parent] <> BLANK()))Return
If(Final = BLANK(), 0, Final)
19 Replies
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:
- Create a filtered table for “Done” items.
- Aggregate the filtered table.
- 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.
If my post answers your query, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are always appreciated.
This is basically what I did, I duplicated the table and filtered it and then rejoined it to the original table.
Hi EaglesTony
Do another Merged step like before (this is the code but you could do it in the editor). Then doing the count as normal but go back in and modify the text.
So your final step looks like:
= Table.AggregateTableColumn(Table.SelectRows(#"Merged Queries1", each [Status]="Done"),"Expanded AgileTeams", {{"Parent" List.Count, "Count of Filtered AgileTeams.Parent 2"}})Basically replacing the original table name with a step that loops over and filters each row.
So I have the following steps:
Merged Queries1
Aggregated Expanded AgileTeams (which is pointing to Merged Queries1 step)
I still need the "Aggregated Expanded AgileTeams" step, as this gives me a count (including Done and not Done), but need this additional step for just "Done" items.
Do I need to insert a step after the "Aggregated Expand AgileTeams" step ?
Hi EaglesTony
My steps look like this:
The code looks like this (based on the date of your previous question):
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Parent"}, #"Filtered Rows", {"Parent"}, "Filtered Rows", JoinKind.LeftOuter),
#"Aggregated Filtered Rows" = Table.AggregateTableColumn(#"Merged Queries", "Filtered Rows", {{"Employee", List.Count, "Count of Employee"}}),
#"Merged Queries1" = Table.NestedJoin(#"Aggregated Filtered Rows", {"Parent"}, #"Aggregated Filtered Rows", {"Parent"}, "Aggregated Filtered Rows", JoinKind.LeftOuter),
#"Aggregated Aggregated Filtered Rows" = Table.AggregateTableColumn(Table.SelectRows(#"Merged Queries1", each [Employee]="ABC-1"),"Aggregated Filtered Rows", {{"Employee", List.Count, "Count of Employee.2"}})
in
#"Aggregated Aggregated Filtered Rows"
Hi EaglesTony
Does this have to be done in the Query Editor?
If you do it in the front end the DAX would be much easier:Employee Count =var curParent = [Parent]RETURNCOUNTROWS(FILTER('Check Table', [Parent] = curParent))Done Employee Count =var curParent = [Parent]RETURNCOUNTROWS(FILTER('Check Table', [Parent] = curParent && 'Check Table'[Status] = "Done"))This would normally work, however the table I am using has 4 types of records in a flatten scenerio, such as:
Region Mgr
---District Mgr
----Mgr
------Employee
Is there a way to only COUNTROWS if the parent is not null ?
Hi EaglesTony
Sure it would be:
Table.AggregateTableColumn(#"Merged Queries", "Filtered Rows", {{"Employee", List.Count, "Count of Employee"},{"Status", List.NonNullCount, "Count of Employee2"}})
