How do I add a filter to a Table.AggregateTableColumn

[EaglesTony]

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.

[asadmd93]

 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.

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. 

[EaglesTony]

This is basically what I did, I duplicated the table and filtered it and then rejoined it to the original table.

[SamWiseOwl]

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]

RETURN

COUNTROWS(FILTER('Check Table', [Parent] = curParent))

Done Employee Count =

var curParent = [Parent]

RETURN

COUNTROWS(FILTER('Check Table', [Parent] = curParent && 'Check Table'[Status] = "Done"))

[EaglesTony]

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 ?

[SamWiseOwl]

Hi @EaglesTony 

Sure it would be:
Table.AggregateTableColumn(#"Merged Queries", "Filtered Rows", {{"Employee", List.Count, "Count of Employee"},{"Status", List.NonNullCount, "Count of Employee2"}})

[SamWiseOwl]

Ahh sorry you meant in the DAX sure:

Employee Count =

var curParent = [Parent]

RETURN

COUNTROWS(FILTER('Check Table', [Parent] = curParent))

Done Employee Count =

var curParent = [Parent]

RETURN

COUNTROWS(FILTER('Check Table', [Parent] = curParent && 'Check Table'[Status] = "Done" && [Parent] <> BLANK()))

[EaglesTony]

This works, sort of..It does give me now the correct number when there are children, however when there aren't it gives me I think a null, as my visual is showing a blank, so I need to somehow replace it with 0, as I want to show a 0.

[SamWiseOwl · Accepted solution]

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]

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]

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]

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.

[EaglesTony]

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"}})

[SamWiseOwl]

What is your very last step?

Should be something like:
in 

#"Renamed Columns3" 

[EaglesTony]

It is just renaming a column.

[SamWiseOwl]

Hi @EaglesTony 

This step

#"Renamed Columns3" = Table.RenameColumns(#"Aggregated Expanded AgileTeams",{{"Count of Expanded AgileTeams.Parent", "SolutionEpicChildCount"}})

Refers to the old table:

#"Renamed Columns3" = Table.RenameColumns(#"Aggregated Filtered Rows",{{"Count of Expanded AgileTeams.Parent", "SolutionEpicChildCount"}})

[EaglesTony]

I think it might be trying to remerge the data and thus filtering it ?...I have this as of now:

#"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"}})

I need to have 2 counts, 1 with count of children and 1 with count of children that are Done.

[SamWiseOwl]

#"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 Filtered Rows",{{"Count of Expanded AgileTeams.Parent", "SolutionEpicChildCount"}})

[EaglesTony]

When I try this:

1) Merged Queries1 step contains 140 rows

2) Aggregated Expanded Agile Teams step contains 140 rows and shows the correct number of children for each

3) Merged Queiries2 step contains 140 rows

4) Aggregated Filtered Rows step only contains 10 rows (which all these rows are the ones that have status of Done)...This is the issue, I don't want to filter on only showing Done, I want all 140 rows with the additional column of Done, so 10 rows will have this new column with a value and the other 130 will have this value as null.

If needed, I could duplicate the table, filter only "Done" items, Group by Parent and then take this new Count column and merge it back to the original table, but wasn't sure if this is effiecent to do.

[SamWiseOwl]

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"