Removing Null Rows but not all of them

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

Common Use Cases

Accepted answers

philipmannering

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.

philipmannering

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...

Filter out null rows.yxmd

All comments

philipmannering

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

Hey @hemant86 . You're welcome.

Filter out null rows.yxmd

hemant86

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703