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

[PhilippeMuniesa]

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

[PhilippeMuniesa]

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

[Jimmy801 · Accepted solution]

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]

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]

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]

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