Advanced filtering in power query - multiple conditions

[zenisekd]

Hi all, 
I bumped into a puzzle that I do not understand...

In power query I have two columns - State (that gives values Cancel, Sales, etc..) and Confirmation Date (gives null or specific date).

I want to filter all rows that contain at the same time Cancel and null. I tried to use Filter Rows - advanced filter and .... it doesnt work. It filters all Cancel and all nulls ... I cant see why... I am not sure about the code, but the logic in the user interface function should clearly work this way, but it doesnt .... or am I missing something?

I managed to find another way to filter the rows, by adding new column and then filtering by it... but I would like to do it in one easy step... not three. Can somebody tell me what is going on?

= Table.SelectRows(#"Added Custom", each [public.sale_order.confirmation_date] <> null and [state] <> "cancel")

[mahoneypat · Accepted solution]

Please try this instead

= Table.SelectRows(#"Added Custom", each not ([public.sale_order.confirmation_date] = null and [state] = "cancel"))

Pat

[DU123]

Thanks. I have been having the same problem.

I have a table where Number column and Amount column have numerical values. If both values are zero ("0"), I want them filtered out.  But if one of them has a non-zero value, I want the line retained. Below is the advanced filter.

The Advanced Filter creates the following, which does not give me the desired result:

= Table.SelectRows(#"Expanded MasterTBL", each [Number] <> 0 and [Amount] <> 0)

Using the solution above, I've altered the filter to be (changes highlighted):

= Table.SelectRows(#"Expanded MasterTBL", each not ([Number] = 0 and [Amount] = 0))

This solution works. Thanks 🙂

[zenisekd]

You are the boss, sir 🙂 

However, do you think, that the user interface window just doesn't function properly (giving incorrect code), or am I doing something wrong?

Thanks