Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ，

     

    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!

     

v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi Anonymous ，

 

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!

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      v-kelly-msft
      Community Support
      6 years ago

      Hi Anonymous ，

       

      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!