List.Contains() is slow

[jhsimb]

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

[Anonymous]

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

--Nate

[AlienSx]

@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))

[Anonymous]

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

ALWAYS use List.Buffer in your List.Contains!

--Nate

[jhsimb]

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

[AlienSx]

@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]

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

[slorin]

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