How to filter records only where four columns don't contain null or zero

[sd_kevin]

Hi,

I am trying to only show records which contain data in the four columns with amounts (<> null and <> 0). I tried:

Table.SelectRows(#"Filtered Rows2", each
    [Amortization] <> null  and [Amortization] <> 0 and [BookCost] <> null and [BookCost] <> 0 and
    [Depreciation] <> null and [Depreciation] <> 0 and [AllocatedExpense] <> null [AllocatedExpense] <>0
)

but it did not return any records. It works with I only apply that filter to one column but not when I am evaluating four. It seems like a straight forward thing to do and I can't find anything on the forum or google.

From the screenshot, I am trying to exclude rows 5-8.

Need to exclude rows 5-8 only

Thank you for your help!

[Dhacd]

Hi @sd_kevin 

Try the below code.

 ([Amortization] <> null  or [Amortization] <> 0) and ([BookCost] <> null or[BookCost] <> 0) and
    ([Depreciation] <> null or[Depreciation] <> 0) and ([AllocatedExpense] <> null or [AllocatedExpense] <>0)

If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.

Regards,

Atma.

[sd_kevin]

Unfortunately that did not remove any records.

[Dhacd]

@sd_kevin , Can you check whether the  [Amortization] field is totally blank then the code above will not provide you with a solution.

If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.
Regards,
Atma.

[sd_kevin]

when you say totally blank are you saying blank verus null? The [Amortization] field has either amounts or null; there are not blank values.

Thank you

[amitchandak · Accepted solution]

@sd_kevin , Try like

not( ([Amortization] = null or [Amortization] = 0) and ([BookCost] = null or [BookCost] =0) and
([Depreciation] = null or[Depreciation] = 0) and ([AllocatedExpense] = null or [AllocatedExpense] =0) )

[sd_kevin]

Thank you so much! That worked!!