Hi All --- I have below sample table which i need to transform such a way so i can eliminate unwanted data. So using filter block and putting conditional statement i want to filter out only those rows which contain "My Name is 1", "Income", "Outcome" and "Total Pending" from column1 and also rows contains "Gross Income" , "Net Income" and "Tax" from column3, Column4 and Column5. Please advise me the solution. Column1Column2Column3Column4Column5AllNullNullNullNullNullNullNullNullNullNullNullNullNullNullNullNullNullNullNull2023-03-31NullNullNullNullNullNullNullNullNullNullNullNullNullNullNullNullNullNullNullMy Name is 1NullNullNullNullNullNullNullNullNullNullNullGross IncomeNet IncomeNullNullNullNullNullTaxIncome2023-01-3110000001000000NullNullNullNullNullNullNullNullNullNullNullNullNullNullNullNullOutcomeNullNullNullNullNullNullNullNullNullNullNullNullNullNullTotal PendingNull10000001000000Null

I need to filter out the data with conditional statement

Hi All --- I have below sample table which i need to transform such a way so i can eliminate unwanted data.

So using filter block and putting conditional statement i want to filter out only those rows which contain "My Name is 1", "Income", "Outcome" and "Total Pending" from column1 and also rows contains "Gross Income" , "Net Income" and "Tax" from column3, Column4 and Column5. Please advise me the solution.

Column1	Column2	Column3	Column4	Column5
All	Null	Null	Null	Null
Null	Null	Null	Null	Null
Null	Null	Null	Null	Null
Null	Null	Null	Null	Null
2023-03-31	Null	Null	Null	Null
Null	Null	Null	Null	Null
Null	Null	Null	Null	Null
Null	Null	Null	Null	Null
My Name is 1	Null	Null	Null	Null
Null	Null	Null	Null	Null
Null	Null	Gross Income	Net Income	Null
Null	Null	Null	Null	Tax
Income	2023-01-31	1000000	1000000	Null
Null	Null	Null	Null	Null
Null	Null	Null	Null	Null
Null	Null	Null	Null	Null
Outcome	Null	Null	Null	Null
Null	Null	Null	Null	Null
Null	Null	Null	Null	Null
Total Pending	Null	1000000	1000000	Null

Help

Accepted answers

FinnCharlton

Hi @Bansi08 , you are using AND operators in this statement. That means that data will only be filtered out if ALL of those statements are true, i.e. Column 1 contains 'My Name is 1','Income','Outcome' and 'Total Pending'.

I think you want to replace the AND operators with OR. This will filter out rows where ANY of these statements are true.

Hope this helps!

All comments

Hi Thanks for solution. but would it be possible to use conditional statement here or any other way for filtration of the data as per requirement. Since my requirement is "i want to filter out only those rows which contain "My Name is 1", "Income", "Outcome" and "Total Pending" from column1 and also rows contains "Gross Income" , "Net Income" and "Tax" from column3, Column4 and Column5" from above table.

I am trying to avoid too many filter blocks for data filtration.

Bansi08

I am trying use this formula using actual column names but unable to filter out data as expected.

Contains([Column1], "My Name is 1") AND
Contains([Column1], "Income") AND
Contains([Column1], "Outcome") AND
Contains([Column1], "Total Pending") AND
Contains([Column3], "Gross Income") AND
Contains([Column4], "Net Income") AND
Contains([Column5], "Tax")

FinnCharlton

I think you want to replace the AND operators with OR. This will filter out rows where ANY of these statements are true.

Hope this helps!

Bansi08

Thanks replacing AND with OR worked for me.

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157