Forum Discussion

PhilippeMuniesa's avatar
PhilippeMuniesa
Icon for Resolver I rankResolver I
5 years ago
Solved

Filter by list or by arguments -- very different processing times

 
hello,
 
I'm trying to filter a table and I don't understand the origin of the difference in processing time between two methods.
So I'm looking for an effective method.
 
The problem is to filter a table ("Ouvre_FEC1" -  201878 rows, filter result above 32000 Rows) according to one or more filters indicated by the user in an excel table.
This table is retrieved as a list in Power Query.
The number of filters can vary from 1 to X.
The name of the list obtained is named Liste_JX_Selection_Liste 
 
Example
 
List
ACHR
ACH
 
I then filtered the original table according to two methods.
=====1==
let
           Source - Ouvre_FEC1,
           Filtre_Selon_Jx_Selectionnes - Table.SelectRows (Source, each List.Contains (Liste_JX_Selection_Liste, [JournalCode])
In
           Filtre_Selon_Jx_Selectionnes 
=====
 
but this method is extremely slow to return the result in excel compared to the following method: 
 
let
Source - Ouvre_FEC1,
        // get number of filters from list
        Nbr_Filtre - List.Count (Liste_JX_Selection_Liste),
        // get parameters
        Filtre_1 - Liste_JX_Selection_Liste {0},
        Filtre_2 - Liste_JX_Selection_Liste {1},
 
 
        Filtre_Selon_Jx_Selectionnes - Table.SelectRows (Source, each ([JournalCode] - Filtre_1 or [JournalCode] - Filtre_2))
 
In
         Filtre_Selon_Jx_Selectionnes 
 
=======
 
This second method recovers as a variable the different items on the list, and it returns the result much faster (almost instantly). 
 
On the other hand, I stumble on the method that allows to pass a number of parameters of which I do not know the number since it is the users who choose them.
 
Thank you in advance for the valuable help you can give me
 
 
Philippe Muniesa
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello PhilippeMuniesa 

     

    try to change approach 1 like this

    let
           Source - Ouvre_FEC1,
           BufferList = List.Buffer(Liste_JX_Selection_Liste),
           Filtre_Selon_Jx_Selectionnes - Table.SelectRows (Source, each List.Contains (BufferList , [JournalCode])
In
           Filtre_Selon_Jx_Selectionnes

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

     

5 Replies

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hello PhilippeMuniesa 

     

    try to change approach 1 like this

    let
           Source - Ouvre_FEC1,
           BufferList = List.Buffer(Liste_JX_Selection_Liste),
           Filtre_Selon_Jx_Selectionnes - Table.SelectRows (Source, each List.Contains (BufferList , [JournalCode])
In
           Filtre_Selon_Jx_Selectionnes

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

     

    • PhilippeMuniesa's avatar
      PhilippeMuniesa
      Icon for Resolver I rankResolver I
      5 years ago

      Jimmy,

      it works perfectly and much faster. I had not yet been able to realize the advantage of list.buffer or table.buffer. here is what is made for List.Buffer.

      However, I cannot explain the very big difference in processing time for a list that has only two elements.

      2mn, 14sec in the original request

      13 sec in the request you propose with List.Buffer ...

       

      Thanks a lot

       

      Philippe

    • PhilippeMuniesa's avatar
      PhilippeMuniesa
      Icon for Resolver I rankResolver I
      5 years ago

      Jimmy,

       

      it works perfectly and much faster. I had not yet been able to realize the advantage of list.buffer or table.buffer. here is what is made for List.Buffer.

      However, I cannot explain the very big difference in processing time for a list that has only two elements.

      2mn, 14sec in the original request

      13 sec in the request you propose with List.Buffer ...

       

      Thanks a lot

       

      Philippe

      • Jimmy801's avatar
        Jimmy801
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Hello PhilippeMuniesa 

         

        List.Buffer is buffering the data and therefore the engine can access much faster. When you don't use List.Buffer the engine has to read the list on every row when filtering.

         

        BR

         

        Jimmy

  • PhilippeMuniesa's avatar
    PhilippeMuniesa
    Icon for Resolver I rankResolver I
    5 years ago

    Thank you Jimmy

     

    Can you explain to me why, when I use Table.buffer when creating or modifying a table, and then I merge this table put in buffer with another table (in the same requete), it seems to me that the processing time is much longer ???

     

    Sincerly

     

    Philippe