Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filtering a query respect a value list for a column

Hi,

I've a query that I need to filter by a column based on a value list.

I'd like to avoid to repeat more times "equals to" + "or" + "equals to" + "or" operations to implement a such filter.

Does it exist a simplified manner to perform that a column value could be equals to one of the values belonging to a list?

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

     

    You can use List.Contains, put the numbers in a list -  I put the values in the list exlist

     

    Table.SelectRows( #"Changed Type",  each not List.Contains(exList,[Column1]))

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

     

    You can use List.Contains, put the numbers in a list -  I put the values in the list exlist

     

    Table.SelectRows( #"Changed Type",  each not List.Contains(exList,[Column1]))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Vera,

      thanks for your reply.

      I've used the statement without "not" operator, to maintain the rows that match with the list.

      But it seems that the performance of the data transformation logics are worsened after adding List.Contains.

      Any idea?

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        5 years ago

        Try adding ad step before the List.Contains step where you buffer the exList

         

        For example - #"New Step" = List.Buffer(exList)

         

        And then use #"New Step" instead of exList in your query. Note the List.Buffer needs to be in the same query as described.

         

        Pat