cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JuanSombrero
Frequent Visitor

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 to filter my fact table so I only keep those products that are in the product list. Is there a way to pass the values in the list as filter argument in the Table.SelectRows statement?

 

So that next time, when the values change in the list, they are all passed to the filter argument?

 

Thx,

Juan

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

This would be the way:

 

= Table.SelectRows(factSales, each List.Contains(requiredProductNumbers, [Product]))
Specializing in Power Query Formula Language (M)

View solution in original post

12 REPLIES 12
Rygaard
Resolver I
Resolver I

This dont work - Because people have imported their table as a table and not as a list.

Rygaard_0-1618575937664.png

if you "table" do not have a "list" icon.. it wont work.

 

so the "list" of parameterse is a table when you import it - but you can easy chang this

 

  1. click the table you want to use a list
  2. click Transform tab (next to home)
  3. select convert to list

NOW you can use it  as a list

 

Rygaard_1-1618576101724.png

 

= Table.SelectRows(#"Omdøbte kolonner", each List.Contains(Tabel4,[Vejesag]))

 

 

 

 

 

wardt
Frequent Visitor

Just wondering why you wouldn't use an Inner Join to accomplish this? That seems like the simple way to do it. Do the Inner Join and then delete the column representing the "second list" (which you would convert to a table, either natively in the sheet where it lives or as a quick operation in PQ to convert list to table prior to the join).

nickjordan32
Frequent Visitor

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

JulieFoucault
New Member

Hello,

 

The initial solution does work. Here are some details to make it work.

 

In Power Query, you must load a table containing your facts (lots of rows) and an other table containing the filter criterias (a few rows). Your Filter table must be converted to a List. The filter table will contain only one column, right-click on the column header and select Drill Down: the filter table is now a List.

 

List.Contains already includes the notion of "any". The function will go through each row in the fact table (in a specified field) and look for any of the values listed in the Filter List.

 

List.ContainsAny must be used to compare two lists, not a applicable for the example listed above.

 

Here is an example using BOTH functions:

My Fact Table named CRM contains 3 fields: Client, Date, Message

My Filter Table named Filter contained 1 field with 2 entries: "waiting" and "pending".

The Filter Table was drilled down to become a List.

 

The Message field contains different entries such as 

- waiting

- pending

- waiting for call back

- resolved

 

You must select New Source > Blank Query and type the following: 

 

SCENARIO 1

= Table.SelectRows(CRM, each List.Contains(Filter, [Message]))

The resulting table will contain all 3 fields from CRM table, and only the rows where the Message value is an exact match (this is case-sensitive) to either :

"waiting"

OR

"pending"

 

SCENARIO 2

If I want my filter to look into any of the words listed in a Message, I can transform each entry under Message into a "list" with Text.Split function. I will use " " (space) as a delimiter, therefore a Message value like "waiting for call back" will become a list of those 4 words.

 

= Table.SelectRows(CRM, each List.ContainsAny(Filter,Text.Split([Message]," ")))

The resulting table will contain all 3 fields from CRM table, and only the rows where the Message value contains either "waiting" or  "pending" :

"waiting"

"pending"

"waiting for call back"

 

Julie at Daxel.ca, offering training in Ottawa, Canada

 

@Drew248 

I am not an expert on M code but am trying to accomplish the same thing.  Can you please show me with visuals where this code would need to be added?

I got it figured out!! Took me an hour but you gotta learn somehow!  Thanks!

MarcelBeug
Community Champion
Community Champion

This would be the way:

 

= Table.SelectRows(factSales, each List.Contains(requiredProductNumbers, [Product]))
Specializing in Power Query Formula Language (M)

View solution in original post

Not sure how this is the correct answer.  Executing this results in:

 

Expression.Error: We cannot convert a value of type List to type Table.
Details:
Value=List
Type=Type

 

I tried doing the same thing and it produces as I have a list I am using to try and filter a table in the same way.  Other examples mention trying to using a query parameter instead--seems like it is much more laborious than it should be.

Anonymous
Not applicable

Having the same issue here, have been trying to figure this out all morning.

No luck! If someone could elaborate futher it would be appreciated greatly.

For those that have trouble with List.Contains, you might need Table.Contains instead.

 

Had the same requirement for a project using Power Query for Excel.  The table containing the values to be passed to the filter was in a table on an Excel sheet, so end users could update the values as needed.  I created a query to reference that table, "tblAgents" with a header value of "AgentID."  To help understand the syntax, let's assume the table that I want to filter has a column of "AgentIdentifier."

 

Here's the code that works for me:

 

#"Filtered Rows" = Table.SelectRows(Source, each Table.Contains(tblAgents, [AgentID = [AgentIdentifier]]))

Drew248
Frequent Visitor

i have tried this method and keep getting zero results, could you send a sample workbook containing this filtering method so i can take a look?

Hello--sorry I missed this somehow--happy to oblige though!!  Here is a code snippett below which should give you what you want:

//
    //Use the parameter list to feed code values...
    //
    tblIncludeStates = Table.SelectRows(tblFromDownload, each List.ContainsAny({MyCodeList} ,{[Outstate_Code]})),
 
..the "MyCodeList" is my list of codes..the "ContainsAny" is the m function/method that did the trick for me..you might also look at the type you used for the list and make sure it coincides with the type you are looking to iterate over.

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!