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
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.
It's actually super easy. You will need to create a separate query and populate that query with the list of values (in this case your parameters), give it a meaningful name and than use it as first parameter of your List.ConainsAny() function.
*** you do not need to wrap the new list in curly braces, just use the name
each List.ContainsAny(listQuery, { [CodeValueField]})I test it on my side it works
Nick -
Great info...
Just wanted to know what if your parameters were text-based? I having issues getting the quotation marks to work
Anonymous My solution was with numeric values. However, List.Contains + All or + Any functions work with strings too. Try wrapping in double quotes the text with quotation marks like so :
Source
a "b" c
= List.ContainsAny(Source, { """b"""})N -