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!