Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

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!



9 - Comet

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

5 - Atom

Very useful Thanks a lot