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

[Masond3]

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 

ID	Col 1	Col 2	Col 3	Col 4	Col 5	Col 6	Total Columns	Total # With values	# [Null] Populated
11111	Y	[Null]	[Null]	[Null]	[Null]	[Null]	6	1	5
22222	Y	Y	Y	Y	[Null]	[Null]	6	4	2
33333	[Null]	[Null]	[Null]	[Null]	[Null]	[Null]	6	0	6

looking forward to your help 

Regards
Masond3

[atcodedog05]

Hi @Masond3 

You can use transpose and summarize combination to get the count. Also hoping your IDs are unique.

Workflow:

Hope this helps : )

[AngelosPachis · Accepted solution]

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