Forum Discussion

JuanSombrero's avatar
JuanSombrero
Frequent Visitor
9 years ago
Solved

Pass a list as filter argument in Power Query

Hi all,   I have a fact table with transactions (sales and product numbers), and a second list of 'required product numbers'. This list is dynamic and can change every time I use the report. I want...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    This would be the way:

     

    = Table.SelectRows(factSales, each List.Contains(requiredProductNumbers, [Product]))
nickjordan32's avatar
nickjordan32
Frequent Visitor
6 years ago

Hey I wanted to let everyone in here know that performed an efficiency test on utilizing the list as filter argument vs simply just using the filter drop down.  My filter list was about 13 - 20 out of probably 100 total unique field values to compare against.  It took significantly longer to refresh the dataset when I utilized the filter list versus the simple drop down filter in query editor.  (10 seconds using the simple method; 59 seconds using the filter list).

 

Does anyone have any thoughts here?  It is clearly a lot easier and cleaner to use that list as the filter argument, but in my situation it significantly increased the refresh time.  Is it possible that I am doing something wrong?  Any comments greatly appreciated.

 

Thanks,

 

Nick