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)
SamWiseOwl's avatar
SamWiseOwl
Super User
2 years ago

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.

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 ?