Best Practice for Filtering a Table by Another Table in Power Query

[IamTDR]

Hi
Just wondering from a performance issue, which method is better and why? Using a 'inner' merger between two tables or filtering a table using a list.

I usually get requests to do a report based on a certain group of customers, and yet the customer table is very large. So, I usually filter the sales table down to just the chosen customers. Both methods mentioned above work, but which is the best practice method? Another option is to load the full table and use DAX to filter for the group of customers. That method does not seem correct to me as that would have power query loading unnecessary records.
I believe the method of using list to filter a table, keeps the query native, so that’s a positive.
Thoughts

[watkinnc]

It depends. The distinct buffered list is wonderful:

= Table.SelectRows(table, each List.Contains(List.Buffer(List.Distinct(NameOfFilterListOrColumn)), [NameOfThisTableColumn]))

That's a very fast filter. But a well indexed db can be pretty fast using an inner join.

As the poet said, "It depends."

--Nate

[IamTDR]

Thanks I'll try to add the List.Buffer to the code.

[v-kelly-msft · Accepted solution]

Hi  @IamTDR ,

You could also use query parameters to filter data before loading :

https://docs.microsoft.com/en-us/power-query/power-query-query-parameters

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[IamTDR]

Thanks
This does seem like another option to filter a table by another query within power query.
I guess all three options performance is the same?

[v-kelly-msft]

Hi @IamTDR ，

Is your issue solved now?Could you pls mark the reply as answered to close it?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft]

Hi  @IamTDR ,

Not exactly,parameters can be used in more senarios,as mentioned in the document,query parameter can be used for functions,when you need a complex filtering with your data ,it would be a best choice.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!