Forum Discussion

EaglesTony's avatar
EaglesTony
Post Prodigy
2 years ago
Solved

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"}})...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago
    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)
EaglesTony's avatar
EaglesTony
Post Prodigy
2 years ago

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 ?

SamWiseOwl's avatar
SamWiseOwl
Super User
2 years ago

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"