Removing Null Rows but not all of them

[hemant86]

Hi Team,

I have few columns which as shown below. At times Alteryx captures null columns from the excel sheet if user has added some data and deleted later. I want to remove all those null rows but keep the actual columns present in the file. Now in below example Column A,B,C & D are the actual columns present in the file but F01, F02 & F03 are automatically created by the tool (may be because there was data once but now deleted). Later it might be possible it would create 5 null columns instead of 3.  Now I cannot use Data Cleanser tool here since while uploading this file I need Col B & Col C to be present since I have added checks/formula for C & D. But I want to remove all F01,F02 & F03 automatically. In future if there are more such columns should be removed but keeping C & D

Appreciate any suggestions.

Col A	Col B	Col C	Col D	F01	F02	F03
123			qwer
345			ddffg
456			sddf
789			sdgg

[PhilipMannering · Accepted solution]

That usually happen when there is formatting left in the deleted cells. So you could either clear formatting in Excel. Or you could use the Dynamic Select Tool with formula,

not regex_match([Name], 'F\d+', 0)

...to dynamically remove all fields starting with capital F followed by 1 or more digits. Think that should do it.

[hemant86]

Thanks @PhilipMannering That's perfect. 👍

[hemant86]

Thanks @PhilipMannering for the solution. Can we do something similar to remove null rows without using Data Cleansing tool. Data cleansing tool is painstakingly slow.

Thanks in advance

[PhilipMannering · Accepted solution]

Hey @hemant86 . You're welcome.

I agree that the Data Cleansing can be inefficient, especially for large datasets. There are a couple of alternatives: basic filter (if you know the names of your columns in advance) or pivoting your data. See attached an example...

[hemant86]

Thanks @PhilipMannering Thanks for taking out time to share the solution. I will try that out.