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?
Solved! Go to Solution.
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.
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.
Hello--sorry I missed this somehow--happy to oblige though!! Here is a code snippett below which should give you what you want:
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]]))