Remove Null Rows

[saachitalwar]

I have data in the form

h1      h2     h3     h4      h5     h6     h7    file name

a        b        c       d       e        f       g       fgghhg.xlsx

[Null] [Null] [Null] [Null] [Null] [Null] [Null] fgghhg.xlsx
[Null] [Null] [Null] [Null] [Null] [Null] [Null] fgghhg.xlsx
[Null] [Null] [Null] [Null] [Null] [Null] [Null] fgghhg.xlsx
[Null] [Null] [Null] [Null] [Null] [Null] [Null] fgghhg.xlsx

I need to keep the rows with data and remove the null rows

I tried using the data cleansing tool, unselected filename and selected the rest then selected remove null rows. It is removing all the data rows but not the null rows somehow.

[binuacs]

@saachitalwar because your file name field is not NULL the data cleansing tool will not work , create a flag using a formula tool and filter it out if your fields are not dynamic

IF IsNull([Field1]) AND IsNull([Field2]) AND IsNull([Field3]) AND NOT IsNull([File Name]) THEN FALSE ELSE TRUE

[chuckleswk]

You can't remove null rows because even when deselecting the FileName it checks the whole row to see if the row itself is Null. Because your File Name is not null, it won't remove it. Unchecking the box on File Name only prevents it from applying the Replace Nulls, Remove Unwanted Characters, and Modify Case settings to that field.

There's several ways to handle this.

• The easiest way is to use the Select tool and uncheck the File Name column and then use the Data Cleansing tool
• If your expectation is that you will always have data in one of those columns (h1-h7) then you just use the filter tool and check to see if that field is null
• The other way is a little more complicated in which you would
  1. Assign a record ID to each record using the Record Id tool
  2. Use a Transpose selecting your RecordID and File Name as the key columns
  3. Use a filter to filter out any Nulls in the Value field
  4. Use the Summarize tool and group by RecordID
  5. Join the output from step 4 to the output from step 1 on the RecordID
  6. Take the Inner Join from the output

 

[flying008]

Hi , @saachitalwar 

A dynamic resolution for you :

[saachitalwar]

Hi, this worked!

But I'm afraid there's another issue as in some cases there is no data so the entire sheet is empty, and of course the filename is deleted as well.

How can that be rectified? I need to get the output as blank in the excel sheet.

[chuckleswk]

I have modified the workflow to use a control container that will only activate when there are no records that can be passed onto the output and will pass along the filename.