Forum Discussion
How do I add a filter to a Table.AggregateTableColumn
- 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)
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
Do another merge step after the "Aggregated Expanded AgileTeams" step.
Expand the data as before using a count.
Then edit that step in either the Advanced editor or the M bar at the top of the page.
Here is what I have, however it is now only showing "Done" items when I click on the "Aggregated Filtered Rows" step.
#"Merged Queries1" = Table.NestedJoin(#"Expanded AgileTeams", {"Parent"}, #"Expanded AgileTeams", {"Parent"}, "Expanded AgileTeams", JoinKind.LeftOuter),
#"Aggregated Expanded AgileTeams" = Table.AggregateTableColumn(#"Merged Queries1", "Expanded AgileTeams", {{"Parent", List.Count, "Count of Expanded AgileTeams.Parent"}}),
#"Merged Queries2" = Table.NestedJoin(#"Aggregated Expanded AgileTeams", {"Parent"}, #"Aggregated Expanded AgileTeams", {"Parent"}, "Aggregated Filtered Rows", JoinKind.LeftOuter),
#"Aggregated Filtered Rows" = Table.AggregateTableColumn(Table.SelectRows(#"Merged Queries2", each [Status]="Done"),"Aggregated Filtered Rows", {{"Parent", List.Count, "Count of Done.2"}}),
#"Renamed Columns3" = Table.RenameColumns(#"Aggregated Expanded AgileTeams",{{"Count of Expanded AgileTeams.Parent", "SolutionEpicChildCount"}})What is your very last step?
Should be something like:
in#"Renamed Columns3"