COUNT all Entries in Columns Dynamically in whole Table

[Anonymous]

Hey there,

I want to count the total entries for all rows and columns within a table. This isn't so hard, however, the table's number of columns will change in the future, and for maintenance purposes I want this formula to be dynamically adapting to the number of columns.

TableA right now, which should return 10 occurences.

TableA
A,B,C,D
o, , ,o
o,o,o,o
o,o,o,  
 , ,o,

And the TableA expanded in the future should return 13 occurences with the same formula.

TableA (2019+)
A,B,C,D,E
o, , ,o,o
o,o,o,o,o
o,o,o, , 
 , ,o, ,o

 Any ideas on this?

 Kind regards,

Igor

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Assume entries in your columns is "o" in text, 

Please have a look at my test

For Table A (has columns: A,B,C,D)

1.Go to Home->Edit Queries, select all columns in this table, 

2.Then select "Transform->Unpivot columns"

3.colse &&apply

4.create a calculated column

count = COUNT(Table1[Value])

5. after add a column"E" in your original data source, cilck "Refresh" button, then the [count] column would count the occurence for all five columns. 

Best Regards

Maggie

[Anonymous]

Hey Maggie,

This works perfect! Thanks for the solution.

Kind regards,

Igor