How to use Filter Data Table activity?

I am a bit confused with how to properly use Filter Data Table activity.

I have an excel sheet, with columns column A, column B, column C, column D, and column E.
Now I was able to use Read Range activity to successfully retrieve the data into a datatable dt_ExcelData.

However, sometimes, there is a human error when the excel sheet was created, and some column data might be missing. Or, a row might contain just column names even in the middle of the data. (yes, as if the column header existed in the middle of the data sheet: Refer to the below image row #10).

If one or two data are missing from a row, we will still process the data.
However, if a row is missing all column A, column B and column C at the same time, or it has column headers, we need to get rid of that row because it is missing too much information, or contains invalid values.

Below is the example of acceptable and unacceptable data. (In the actual Excel data, the last column doesn’t exist. I only added it for explanation.)

Now I want to get rid of all these invalid rows using Filter Data Table and I have the following:

I am trying to keep any rows where “column A” is not empty, or does not equal to “column A”, AND so on. However, this is somehow not working. This is returning the following result!
Capture

Why is it not working? Ideally, the result should be all the valid rows, removing invalid rows.
Can anyone help me correct this?

Hi @tomato25,

I think it might be easier to do the reverse, and instead of keeping valid rows you can try removing the invalid rows. Because currently what you’re filtering is keeping all rows where column A/B/C are not empty AND not equal to their column name, which means you’re not keeping rows where just one or two of the columns are empty (but the other column has a value).

You could use the filtering like:
where column A = “” AND column B = “” and column C = “” for the first invalid check and then
column A = “column A” AND column B = “column B” AND column C = “column C” for the second check.

I’m not too well versed in the filter activity so not sure if that requires two filter activities (one for each check), but play around and see what you get :slight_smile:

1 Like

Thank you! That actually is easier…

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.