Multiple Dynamic List Filters slowing down Power Query

[JacoGrobbelaar]

Hi All,

I want to filter my data in PQ based on multiple criteria with multiple lists.
I achieved this, but my query slowed down a lot. (Before, 1.5mins(Based on 1 criteria with multiple lists, now almost 45mins)

I read in the forum about "List.Buffer", but seeing that my list is dynamic, the query will need to take in new filter criteria anyway. Unless I can be corrected?

Am I using the right logic in the below query?

1. Query

#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each [InvoiceDate] >= Start2 and [InvoiceDate] <= End2),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each (List.Contains(LOB_Parameter,[LOB])=true)),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each (List.Contains(P_Region, [Branch])=true)),
#"Filtered Rows4" = if List.IsEmpty(P_SP)=false then #"Filtered Rows3" else Table.SelectRows(#"Filtered Rows3", each (List.Contains(P_SP,[Salesperson])=true)),
#"Filtered Rows5" = Table.SelectRows(#"Filtered Rows4", each ([TrnYear] <> #"End2 Year"+1)),

2. Dynamic lists input in excel, List in PQ

// I will add the "Department List" to the query when I can get a solution on the speed of list.contains.

A bit of context, a user can select from a dropdown the diferent criteria to filter the Sales database, if user "Blank" out one or all in a list, the filter wont apply to that field.

Thanks for your help.

[watkinnc]

Now you have 44 minutes left to do whatever you'd like.:)

[watkinnc · Accepted solution]

Yes, you should be buffering by those lists. I would add List.Buffer(List.Distinct(Listname)), ...) to each of your List.Contains expressions, (you can ignore the List.Distinct if you know your list is distinct); so:

#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each (List.Contains(List.Buffer(List.Distinct(LOB_Parameter)),[LOB])=true)),

That should speed things up by a little, or maybe 100X, let us know!

--Nate

[JacoGrobbelaar]

It worked perfectly, thank you so much.

Its quicker than the "One criteria with multiple lists" without the buffer. About 40sec.

Thanks again.
Regards

[JacoGrobbelaar]

Thank you, checking it now...

[Anonymous]

you should try to "cross" the table only once.

try somothing like this.

Check the syntax and the names I tried to interprete.

 #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each [InvoiceDate] >= Start2 and [InvoiceDate] <= End2 and [LOB]=LOB_Parameter and [Branch]=P_Region and ([Salesperson]=P_SP or [Salesperson]=null) and [TrnYear] <> #"End2 Year"+1) 

an alternative way, perhaps more efficient, is to build a single row table with the fields you want to filter as column names and by value the values read from the Excel tables.
Then you inner join between this filter table and your sales table.
How many rows and columns do your tables have?

PS

I assumed that the user chooses only one of the list options.
If instead you have to take all the lines that have LOB = ABC and LOB = EFG etc ... then you have to use the List.Contains function (LOB_Parameter, [LOB]), but without the need to add "= true": the result of the function is already a boolean.

PPS

 The alternative is always to use a filter table with one or more rows and join.
If you provide the example tables, someone will propose you the code

[JacoGrobbelaar]

Thanks will try the Cross as well, will let you know, makes sense though.

"an alternative way, perhaps more efficient, is to build a single row table with the fields you want to filter as column names and by value the values read from the Excel tables.
Then you inner join between this filter table and your sales table.
How many rows and columns do your tables have?"
 - I can have a look in this.
 - Depending on the Date range the user enters, but max is 200k rows, with about 10 fields.
 - Also PowerPivot Measures and calculated collumns in the background.

"PPS

 The alternative is always to use a filter table with one or more rows and join.
If you provide the example tables, someone will propose you the code"
 - Will do, if the other solutions doesnt work.