Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jhsimb
Frequent Visitor

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

7 REPLIES 7
Anonymous
Not applicable

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

 

--Nate

@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
Not applicable

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

 

ALWAYS use List.Buffer in your List.Contains!

 

--Nate

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

AlienSx
Super User
Super User

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

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
Super User
Super User

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 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors