Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dslForPBI
Helper I
Helper I

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

@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

View solution in original post

11 REPLIES 11
nickchobotar
Skilled Sharer
Skilled Sharer

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

 

 

 

 

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

@dslForPBI

 

Please post some examples.

 

N -

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

@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

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

 

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
Not applicable

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 -

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.

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

 

Nick -

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.