cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
darentengdrake
Resolver II
Resolver II

List.Contains() is slow

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

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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!

 

View solution in original post

5 REPLIES 5
ImkeF
Super User
Super User

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

v-kelly-msft
Community Support
Community Support

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!

 

View solution in original post

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

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

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.