Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
After struggling with this, seems I need a different approach..looking through several examples I still haven't found one which totally fits. I am trying to filter a table based on values in a list. I want the values I am filtering on to be checked from the list so i am thinking have a function which starts at the begining and iterates through to the end. In short, i want to check the table (column "X") for a value in list "Y" as part of the filter. Here is code snippet with what works currently (hardcoded) and what I want to do:
//Tried to use "ValueList" which is just a list of values to filter on...
//
//Hardcoded list works (below)..
//
tblIncludeStates = Table.SelectRows(tblFromDownload, each List.Contains({543,95,96,1359,1360,1104,1170,1172,93,1248,454,1361,92,97,-4,-3,-5,-7,-8,-2}, [Outstate_Code])),
//
//...but ValueList which is a list of these numbers does not (produces an empty table result)...it produces an empty result
tblIncludeStates = Table.SelectRows(tblFromDownload, each List.Contains(Value,[Outstate_Code])),
..so perhaps some kind of function taking the number of items in the list and moving from top to bottom might work?
Solved! Go to Solution.
Hi @dslForPBI,
I'm glad to hear that you have resolved your issue. Please kindly mark your solution as Answer. It will help others with relevant issues find the answer more easily.
Thanks,
Xi Jin.
Hi @dslForPBI,
I have made some test. However the Power Query expression works fine. Please refer to my sample:
Source table and the list are like:
Then define the expression for the source table:
As you can see, it works. Thus in your scenario, could you please share us pbix file with OneDrive or something else if possible? So I can dig deeper and make some proper tests. If you can't share us your report, could you please share us some detailed screenshots or some sample data which can help us repro your issue?
Thanks,
Xi Jin.
Hello v-xjin-msft,
I was having issues but seems they were solved in another section on this forum. What causes issues--it seems at least in my tests was referencing a list as a parameter. The option that made it work was changin from "List.Contains" to "List.ContainsAny".
Hi @dslForPBI,
I'm glad to hear that you have resolved your issue. Please kindly mark your solution as Answer. It will help others with relevant issues find the answer more easily.
Thanks,
Xi Jin.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |