04-18-2017 11:27 AM - edited 07-22-2021 11:26 AM
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!
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.
Very useful Thanks a lot