How to Add a Count of the Number of Missing Values in each Row of Data

[JessicaS]

We frequently get questions about how to flag rows in a data set that are missing values in any column.

In the example below, we will demonstrate how to add a new column to the data that gives a count of null or empty values per row. The end result will give a count of 1 to each of the highlighted rows in the image. The new column can be used in a filter tool to isolate rows of data that have missing values:

Input:

Output:

To get started, if the data does not have a Record ID or unique identifier for each row, add one in.

Then grab a Transpose Tool. Use the unique identifier or Record ID as the key field and select all data fields except the key field.

Once your data is transposed you can use a formula to add a flag for rows that contain missing or empty values using the below expression:

Then use a Summarize Tool to get counts for each row by grouping by the key field and summing the flag field:

Join the counts back to the original dataset and you're off to the races!

[terry10]

This is very helpful, thank you!

Note a discrepancy - The post says "Then use a summarize tool to get counts for each row by grouping by the key field and summing the flag field:", but the workflow takes the Max of the "Row Contains Empty Values contains empty columns", not the sum. 

[elsaiedwa]

Very useful Thanks a lot