Forum Discussion
AggregateTableColumn with List.Count returns 1 instead of 0
With an aggregation I get an unexpected result. The 'parent' table is as follows:
| ROUTING_ID |
| 1 |
| 2 |
| 3 |
The 'child' table is as follows:
| ROUTING_ID_From | ROUTING_ID_To |
| 1 | 23 |
| 1 | 24 |
| 1 | 25 |
| 2 | 23 |
| 2 | 24 |
Now I do a AggregateTableColumn with a List.Count, and I get as result:
| ROUTING_ID | NrOf_ROUTING_ID_To |
| 1 | 3 |
| 2 | 2 |
| 3 | 1 |
Why has the last row (ROUTING_ID = 3) as number of Routings-To 1 instead of 0?
This is the line of code:
#"Aggregated qryRoutingTransitions_Direct2" = Table.AggregateTableColumn(#"Merged Queries3", "qryRoutingTransitions_Direct2", {{"ROUTING_ID_To", List.Count, "NrOfNextRoutings"}}),Should I include that ROUTING_ID_To must not be null in order to not get counted as 1? Would be strange... If so: how to do that?
I've found the solution: instead of List.Count must be used List.NonNullCount.
3 Replies
I've found the solution: instead of List.Count must be used List.NonNullCount.
Thanks! Big help.
I can confirm that the solution works, but could anybody explain why?! AggregateTableColumn returns empty tables which return empty lists for columns and List.Count of an empty list is 0, after all!
