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]))
wsilva's avatar
wsilva
New Member
7 years ago

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's avatar
Drew248
Frequent Visitor
7 years ago

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?