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


 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:








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!