Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter out row using Table.SelectRows and conditional if/and

Hi all,  I need to drop a row from my table in power query (Excel). I presume I need to filter that row out. The trouble is that other rows also have than same value and I dont want to filter out th...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Anonymous 

     

    You can filter to Bob and 26 and add not like below.

     

    = Table.SelectRows(#"Changed Type", each not ([Indicator] = "bob" and [Total] = 26))
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Anonymous 

     

    You can try the below.

     

    = Table.SelectRows(#"Changed Type", 
        each not ( 
                ( [Indicator] = "bob" and [Total] = 26 ) 
             or ( [Indicator] = "jane" and [Total] = 21 )
             or ( [Indicator] = "jon" and [Total] = 15 )
        ) 
    )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks a lot Mariusz , 

each not() was exactly what I needed. 

I also figured I could add an Index column and then filter out that unique ID. 

Cheers, Roger

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi, can I ask a further question on this, 

 

How can I adjust the code so that this code:

= Table.SelectRows(#"Changed Type", each not ([Indicator] = "bob" and [Total] = 26))
= Table.SelectRows(#"Changed Type", each not ([Indicator] = "jane" and [Total] = 21))
= Table.SelectRows(#"Changed Type", each not ([Indicator] = "jon" and [Total] = 15))

is wrapped in something like a  

each [indicator]
each if then
else if then
else

So I dont have to keep writing '= Table.SelectRows(#' ? 

Thanks, Roger

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

     

    You can try the below.

     

    = Table.SelectRows(#"Changed Type", 
        each not ( 
                ( [Indicator] = "bob" and [Total] = 26 ) 
             or ( [Indicator] = "jane" and [Total] = 21 )
             or ( [Indicator] = "jon" and [Total] = 15 )
        ) 
    )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Great, thanks! That simplifies my code a little :)