cancel
Showing results for 
Search instead for 
Did you mean: 

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

Moderator
Moderator

 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:

input.png

 

Output:

output.png

 

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.  

 

 transpose.png

 

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:

 

form.png

 

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

 

 su.png

 

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

 

image2.png