Cell entry count in another table.

[Anonymous]

Hi,

I have a large table with 165 columns and 170,535 rows called full_stats. Not all the rows have data in them.

I want to create a new table that lists all the column headers from full_stats as rows under one column, and another column containing the number of entries (COUNT) in each of the columns in full_stats.

What would be the best way to go about this?

Thanks in advance,
A. Noob. 

[v-xiaosun-msft · Accepted solution]

Hi @Anonymous ,

Please use Power Query to copy your initial table and paste it as below. 

Then follow these steps in Table(2).

1, Select all columns and then right click and select "unpivot columns".

2, Select the first column and then select "group by", select "OK".

3, You will get the expected output, and the select "close & apply".

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xiaosun-msft · Accepted solution]

Hi @Anonymous ,

Please use Power Query to copy your initial table and paste it as below. 

Then follow these steps in Table(2).

1, Select all columns and then right click and select "unpivot columns".

2, Select the first column and then select "group by", select "OK".

3, You will get the expected output, and the select "close & apply".

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much for you help. That did the trick.

I did however, present me with another issue. After following the steps above, the resulting count column is not accurate for some of the counts. 

In some of them, one that are text fields not numbers, it is giving me a count of the full 170,535. when I know there are nowhere near that many entries in those columns.

This only happens on text data. It is like the empty cells are being counted as well. Is there something I can do to prevent this?

Thanks.

[v-xiaosun-msft · Accepted solution]

Hi @Anonymous ,

Thanks for your message.

If your column is text field, you could replace the blank with "null" before your steps. Here are steps.

Sample data:

Select the column and then click "replace values".

Final output:

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.