Counting How many [Null] in x # of columns for a given row

Question

Hi All,

I am having abit of a mental block and need some help.

I have a very simple ask, i would like to produce a column which counts how many columns (in this scenario Col 1 through to 6) which have have no values in them ie [Null]

below is a sample set of data

IDCol 1Col 2Col 3Col 4Col 5Col 6Total ColumnsTotal # With values# [Null] Populated11111Y[Null][Null][Null][Null][Null]61522222YYYY[Null][Null]64233333[Null][Null][Null][Null][Null][Null]606

looking forward to your help

Regards
Masond3

AngelosPachis · Accepted Answer

Hi @Masond3 ,

You can use a transpose tool to bring your data in a long format, whilst grouping on the ID field. That will return 3 columns, one named ID, one that contains the different column headers and the other containing the values.

If you bring your data in this format, you can then make use of a summarize tool to count the number of columns per ID, count the nulls and the non null values, as shown below

Hope that helps,

Angelos