Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

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

JessicaS
Alteryx Alumni (Retired)
Created

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
Comments
terry10
11 - Bolide

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

elsaiedwa
5 - Atom

Very useful Thanks a lot