Forum Discussion

zenisekd's avatar
zenisekd
Super User
5 years ago
Solved

Advanced filtering in power query - multiple conditions

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)...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Please try this instead

     

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

     

    Pat

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Please try this instead

 

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

 

Pat

 

DU123's avatar
DU123
New Member
1 year ago

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 🙂