Delete Empty Rows When All Fields Are Empty or Null Except One or Two Specific Fields

[taxtechfl]

I am ingesting a number of different input files, and the filename is one field that is created and is therefore never empty/null.  If I use the data cleanse tool, it doesn't have the configuration to select the fields to be considered.  There are two many fields to write a formula.

What is the best way to remove/filter out the empty rows?

I have created a sample file.  The first two columns highlighted in light green contain data derived from the input file, and therefore will never be null/empty.  If all other fields are null/empty, however, the rows should be deleted or filtered out.  The rows highlighted in light orange are the ones that should be dropped/filtered out.

I've considered a few options for achieving this, but I am hoping someone has an idea for the fastest, most efficient way of achieving this as this is a pretty common scenario.  Thank you in advance for helping with this!

[Alter_Shivi]

Add a Data Cleansing, and select Remove Null Rows. You have other options too.

[davidskaife · Accepted solution]

Hi @taxtechfl 

As the Data Cleansing Tool won't work, try this solution. First add in a Record ID, then Transpose the data (Key Columns being Record ID, Filename, and Period), then Summarize (grouping by Record ID, summing 'Value' column), then filter out any where the summed value is null. Join this filtered dataset back onto the original dataset (joining on Record ID, removing all right hand side fields), finally sort on Record ID and deselect any fields not required.

This should also cover for when any additional columns come in, so its dynamic.

Workflow attached

[Qiu]

@taxtechfl 
Another approach with the combination of Transpose and Crosstab.
It will be good if we can specify the column ranges that defines "Null Rows" 😁

[flying008]

Hi, @taxtechfl 

If you don't like transpose for this case, another dynamic solution for you :

[taxtechfl]

Thank you for the solution.  I really appreciate it!  It works well and is pretty quick and easy to build and adapt.  Have an awesome day!

[taxtechfl]

Thank you, everyone, for your quick responses and solution options/inputs.  The Alteryx Community is amazing, and you all exemplify that!  🤗

[Qiu]

@taxtechfl 
Glad to be any help.
I have to agree with you that The Alteryx Community is amazing. 😁