Forum Discussion
Best Practice for Filtering a Table by Another Table in Power Query
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
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,
KellyDid I answer your question? Mark my post as a solution!
6 Replies
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
Thanks I'll try to add the List.Buffer to the code.
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,
KellyDid I answer your question? Mark my post as a solution!
