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

 

  • zenisekd's avatar
    zenisekd
    Super User
    5 years ago

    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

  • 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 🙂