Forum Discussion

jhsimb's avatar
jhsimb
Frequent Visitor
2 years ago

List.Contains() is slow

I tried to filter a column based on a list

I used List.Contains() but it is very slow

 

= Table.SelectRows(Medidas, each (List.Contains(Resta,[Cliente])=true ))

 

Please your help with a faster List.Contains because after this step I need to collapse that column

Thanks

Need Help
Show and Tell

7 Replies

  • slorin's avatar
    slorin
    Super User
    2 years ago

    Hi,

    use Table.NestedJoin with JoinKind.Inner

     

    = Table.NestedJoin(Medidas, {"Cliente"}, Table.FromColumns({Resta},{"Resta"}), {"Resta"}, "JoinColumn", JoinKind.Inner)

    then remove column "JoinColumn"

    Stéphane 

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    jhsimb we don't see what is Resta. If it's a result of some transformation/calculation then try to List.Buffer it beforehand. I would also give a try to List.PositionOf alternative with Occurrence.First like this:

    = Table.SelectRows(Medidas, each List.PositionOf(Resta_buffered, [Cliente], Occurrence.First) <> -1 ) where Resta_buffered = List.Buffer(Resta) outside of your expression. 

    • jhsimb's avatar
      jhsimb
      Frequent Visitor
      2 years ago

      Rest is a table. A table that was transformed in a list using Convert to List.

      How do I use List.Buffer in this case?

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Definitely use List.Buffer, as in each List.Contains(List.Buffer(LookupList), [Column])

     

    ALWAYS use List.Buffer in your List.Contains!

     

    --Nate

    • jhsimb's avatar
      jhsimb
      Frequent Visitor
      2 years ago

      I use List.Buffer and it works but after when I collapse that column, very slow again.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    = Table.SelectRows(Medidas, each List.Contains(List.Buffer(Resta),[Cliente]))

     

    --Nate

    • AlienSx's avatar
      AlienSx
      Super User
      2 years ago

      Anonymous I would List.Distinct(Resta) before buffering and buffer outside of Table.SelectRows. My tests show that List.Contains works even faster than List.PositionOf on the same data.

      But the fastest one is to create a record with unique Resta data as field names and "true" as value then 

      Table.SelectRows(values, each Record.FieldOrDefault(resta_record, Text.From([Cliente]), false))