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 |
Solved! Go to Solution.
-
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.
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")
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!
Thanks replacing AND with OR worked for me.