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!
Why is it not working? Ideally, the result should be all the valid rows, removing invalid rows.
Can anyone help me correct this?