Forum Discussion
Using a list parameter as a table filter in M language
You nee to use List.ContainsAny() function. *** MAKE SURE you wrap your field name in curly brackets.
= Table.SelectRows(
PreviousStep,
each List.ContainsAny( { 543,95,96,1359,1360,1104 } , { [CodeValueField] }
))Results returned :
543 95 96 1359 1360 1104 where CodeValueField field contains all the values below:
CodeValueField
543 95 96 1359 1360 1104 1170 1172 93 1248 454 1361 92 97 -4 -3 -5 -7 -8 -2
Sure..here is what I am trying to do with some code snippets (and comments):
//
//..using hardcoded values in a list works (shown 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}, [CodeValueField])),
//
//..supplying a list (either by constructing it, transforming or using a different built-in function)
//does not (produces an empty table result), making me wonder is there something special about "List.Contains" I am missing...
//..below "ValueList" is a list of the same values that is 20 items long and "CodeValueField" is a field
//in a table that has the values I want to filter the table on...
//
tblIncludeStates = Table.SelectRows(tblFromDownload, each List.Contains(ValueList,[CodeValueField])),
You nee to use List.ContainsAny() function. *** MAKE SURE you wrap your field name in curly brackets.
= Table.SelectRows(
PreviousStep,
each List.ContainsAny( { 543,95,96,1359,1360,1104 } , { [CodeValueField] }
))Results returned :
543 95 96 1359 1360 1104 where CodeValueField field contains all the values below:
CodeValueField
543 95 96 1359 1360 1104 1170 1172 93 1248 454 1361 92 97 -4 -3 -5 -7 -8 -2 Hey nickchobotar,
Sorry, that doesn't work..what I am trying to do is use a list instead of providing hardcoded values. The function works fine if you have the values hardcoded..I am trying to have it work like this:
each List.ContainsAny(myListNameHere, { [CodeValueField]})
..reason for this is so that it can be a parameter (instead a hardcoded value)
My bad--it does work!! I must have had an issue transforming to a list but just ran it again reference another parameter list and it worked!! Thanks so much for the help, I will accept this as the solution.