List.Contains() is slow

[darentengdrake]

Hi,

I am using = Table.SelectRows(Table1, each List.Contains(List, [UID])) to filter out 300 categories from a table that has 1.6mil rows. However, it is loading very slow when I apply the query whereas the 1.6mil row table has already finished loading. Theorectically, the new table should have less than 1.6mil rows, but why is it taking forever to load? Is there a better way doing it?

Thanks!

Daren

[ImkeF]

Hi @darentengdrake  

this could be caused by the list not being buffered.

So whatever step you're referencing with "List", make sure to wrap it into a List.Buffer-function.

[kemiogrst]

I would like to know how many items a buffered list can perform well before starting to get slow when called inside a "List.Contains" function. I tried with about 5k items and it seems good, but not with 350K items.
I usually use buffered lists to filter values in tables with codes like this:
If List.Contains(list, [field]) then true else false

Thanks in advance.

[v-kelly-msft · Accepted solution]

Hi @darentengdrake ，

Are you using a direct query mode?Why not filter the table in table view?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[darentengdrake]

Hi @v-kelly-msft 

I am using import mode. I could do the filter in table view using LOOKUPVALUE. Is there a difference between the filter in table view vs query mode in terms of performance and speed?

Daren

[v-kelly-msft]

Hi @darentengdrake ，

If you use M query for calculation,it will only keep the last data after being filtered,and the calculation will be executed in each rows.But if you choose to filter in table view,it wont destroy the data integrity,1.6mil rows of data will still stored in memory and you can first make a filter to pick out the data you need for calculation,no need to execute it in each rows,which will shorten the time for calculation.

However,if it's a need to do a M query,better refer to @ImkeF 's suggestion,this will also help to reduce the time.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

Likely having to do some kind of table scanning. @ImkeF ?