community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Community v19.9

The latest release includes several enhancements designed to improve your Community experience!

Learn More

How to Add a Count of the Number of Missing Values in each Row of Data

Moderator
Moderator
Created on

 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:

2019-06-18_15-48-33.png

 

Output:

 

2019-06-18_15-49-32.png

 

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.  

 

 2019-06-18_15-50-27.png

 

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:

 

2019-06-18_15-52-03.png

 

Then use a Summarize Tool to get counts for each row by grouping by the key field and summing the flag field:

 

 2019-06-18_15-52-44.png

 

Join the counts back to the original dataset and you're off to the races!

 

2019-06-18_15-53-46.png

Attachments