Using a list parameter as a table filter in M language

[dslForPBI]

I have an M language query in which I would like to use list values from a parameter list. Each attempt causes only the default or current item to be read. Is it possible to pull all the items from the list? This is preferred rather then having a hard-coded number of items. Here is a code snippet:

//Trying to have all the 'QryCodes' evaluated as if they were included in this way...

//tblIncludeStates = Table.SelectRows(tblFromDownload, each List.Contains({{543,95,96,1359,1360,1104},[Outstate_Code]))

//

tblIncludeStates = Table.SelectRows(tblFromDownload, each List.Contains(QryCodes,[Outstate_Code]))

[nickchobotar]

@dslForPBI

You will have to create a separate list of all of your parameters and then invoke your query as a separate custom function. Here is an example

https://www.youtube.com/watch?v=iiNDq2VrZPY  // 12 minute describes multiple parameters

Nick-

[dslForPBI]

Thanks for the reply...the only parameter which is relavant for me in my case is list of values, as i have list of values I need to pass to evaluate.  I have searched and it appears creating a function is what is suggested.  Any examples of a custom function which takes a list as an arguement or at least tap into elements in the list (like "mylist[i]").

[nickchobotar]

@dslForPBI

Please post some examples.

N -

[dslForPBI]

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])),

[nickchobotar · Accepted solution]

@dslForPBI

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

[dslForPBI]

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)

[nickchobotar]

@dslForPBI

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 -

[Anonymous]

Great info...

Just wanted to know what if your parameters were text-based? I having issues getting the quotation marks to work

[nickchobotar]

@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 -

[dslForPBI]

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.

[nickchobotar]

@dslForPBI  Great. Thank you! Glad to hear it worked out for you.

Nick -