cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JVos
Helper IV
Helper IV

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_FromROUTING_ID_To
123
124
125
223
224

 

Now I do a AggregateTableColumn with a List.Count, and I get as result:

ROUTING_IDNrOf_ROUTING_ID_To
13
22
31

 

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?

1 ACCEPTED SOLUTION
JVos
Helper IV
Helper IV

I've found the solution: instead of List.Count must be used List.NonNullCount.

View solution in original post

2 REPLIES 2
Chris12
Advocate I
Advocate I

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!

JVos
Helper IV
Helper IV

I've found the solution: instead of List.Count must be used List.NonNullCount.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Kudoed Authors