cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dslForPBI Regular Visitor
Regular Visitor

Using a list parameter as a table filter in M language

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

1 ACCEPTED SOLUTION

Accepted Solutions
nickchobotar Established Member
Established Member

Re: Using a list parameter as a table filter in M language

@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
11 REPLIES 11
nickchobotar Established Member
Established Member

Re: Using a list parameter as a table filter in M language

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

Re: Using a list parameter as a table filter in M language

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 Established Member
Established Member

Re: Using a list parameter as a table filter in M language

@dslForPBI

 

Please post some examples.

 

N -

dslForPBI Regular Visitor
Regular Visitor

Re: Using a list parameter as a table filter in M language

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 Established Member
Established Member

Re: Using a list parameter as a table filter in M language

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

Re: Using a list parameter as a table filter in M language

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)

dslForPBI Regular Visitor
Regular Visitor

Re: Using a list parameter as a table filter in M language

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 Established Member
Established Member

Re: Using a list parameter as a table filter in M language

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

nickchobotar Established Member
Established Member

Re: Using a list parameter as a table filter in M language

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

 

Nick -