Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
I've found the solution: instead of List.Count must be used List.NonNullCount.
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!
I've found the solution: instead of List.Count must be used List.NonNullCount.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.