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 the other ones too. For example, I want to filter out 

[Total]<>26 where in the same row the [Indicator] is "bob"

I do not want to filter out [Total]<>26 for "sally" and "jane"

I do not want to filter out "bob" if total does not equal 26 (since bob has other rows in other years I want to keep

 

I tried this

#"myfilter" = Table.SelectRows(#"previous step", each ([Total] <> 26 and [Indicator] <>"bob"))

But of course this filtered out all the 26 and all the bob entries...How do I write this please?

Best, Roger

 

 

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

     

8 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    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

     

    • 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Mariusz,

      What code would you use if within [Indicator] column the rows had a nested List or Table and you wanted to 'select' only the rows had items in the List. For example [Indicator] contained "Bob" and "Sally" as rows in either the nested List or nested Table?

    • Yondice's avatar
      Yondice
      Advocate I
      3 years ago

      This solved my problem.

      Side lesson learned: if you're renaming columns, don't use parentheses or brackets in the new name. It will screw with subsequent transformation steps.

  • lagha's avatar
    lagha
    New Member
    4 years ago

    How can i select reow different from list, i try this but it doesn't wor?

    = Table.SelectRows(#"Colonne conditionnelle ajoutée2", each [Date de base] <> #"date du jour -30"=true)

    thnks