This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 04-18-201711:27 AM - edited on 06-18-201902:54 PM by SydneyF
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!