finding nulls

[becki]

i'm trying to identify, in hundreds of fields, which one have any nulls in them.  i tried this:

https://community.alteryx.com/t5/Data-Preparation-Blending/Only-show-me-rows-with-a-blank-field/m-p/...

but that gives me any record which has any null, in any field, in the entire record.  what i'm looking for is find all columns (fields) which have any nulls in them.  i tried a summarize -> selected all fields and did "count null" but even for fields which were completely empty, the result was 0.  this is on alteryx 11.5, if that matters.  thank  you in advance

becki kain

[Dynamomo]

Hi @becki,

Have you thought about using the Basic Data Profile tool in the Data Investigation tools?  I think it must have moved out of the Lab recently and into that category so you might not be aware of it.

You can run that on the file, then place a filter after where Name="Nulls" and Value !="0".  That will give you a list of the fields with nulls and the number of records with nulls in that field.

[gnans19]

@becki Try this one. You can control how many columns you want to consider.

[becki]

thanks!  I will and post back!

[danrh]

If you strictly need a list of all fields with null values in them, this should get you pretty close:

The Transpose puts all the column names in one column with a second column with the values.  THEN I'd do a Summarize, grouping by Name and CountNull-ing the Values.

Hope it helps!

[amavila]

This is awesome! It helped me a lot.

[TGoncalves]

This is a very simple and useful solution. 

Thank you very much @danrh