Unable to use parameters in a list.contains

[chipchidster]

I am sure I am being stupid, but I cannot work out how to get this to work and so I am hoping someone knows what I am doing wrong.

I have a large if/else statement written using powerquery/m code that assigns a tribe name based on a project key (see below).

if List.Contains({"POS","PRS","PDS"},[project_key]) then "PIM Mission"
else 
if List.Contains({"CUS","SCV","LS","CL"},[project_key]) then "Attract, Onboard, Service & Retain"
else
"no tribe"

I would like to replace these lists with parameters so that the tribe allocations are easier to modify if needed.  However if I try and use a parameter instead, it doesn't work.

if List.Contains({paramPIMSquads},[project_key]) then "PIM Mission"
else 
if List.Contains({paramAOSRSquads},[project_key]) then "Attract, Onboard, Service & Retain"
else
"no tribe"

Perhaps niaively I was expecting pbi to drop the param in a text string but it doesn't appear to do that.  Am i being stupid or is this one of those really odd pbi deficiencies?

[chipchidster · Accepted solution]

Thank you to both contributors who offered up solutions - I did finally manage to get this working, and it was far simpler that I thought.  I was using List.Contains when I should have been using Text.Contains.  Thank you to @v-yohua-msft for pointing that out in his reply.  Just swapped that and everything works like a charm.

[v-yohua-msft]

Hi, @chipchidster 
Based on your information, I create sample table:

Create parameters:

Then create new custom column, replace List.Contains with Text.Contains, here is M expression:

if Text.Contains(paramPIMSquads, [project_key]) then "PIM Mission"
else if Text.Contains(paramAOSRSquads, [project_key]) then "Attract, Onboard, Service & Retain"
else "no tribe"

Here is my preview:

Or you can create query, like this:

Use the following expression:

if List.Contains(Query1, [project_key]) then "PIM Mission"
else if List.Contains(Query2, [project_key]) then "Attract, Onboard, Service & Retain"
else "no tribe"

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @chipchidster - You're on the right track! The issue stems from how Power Query handles parameters and lists. When you replace the hardcoded list of values with a parameter, Power Query treats it differently.

can you check function

if List.Contains({paramPIMSquads}, [project_key]) then "PIM Mission"

Instead of wrapping the parameter in curly braces, use the parameter directly as it's already a list

if List.Contains(paramPIMSquads, [project_key]) then "PIM Mission"
else if List.Contains(paramAOSRSquads, [project_key]) then "Attract, Onboard, Service & Retain"
else "no tribe"

This will allow you to easily modify the lists later by adjusting the parameters instead of rewriting the entire if statement. try this and i hope it works.

[chipchidster]

No, that doesn't work.  If I use the parameter like that it throws the following error:

Expression.Error: We cannot convert the value ""CUS","CL","SCV","LS..." to type List

I have tried using Text.toList(paramAOSRSquads) but that doesn't work either, and I get the following error:

Expression.Error: We cannot apply an index of type Text.
Details:
Value=[Function]
Index="CUS","CL","SCV","LS"

 I have also tried with the list entries in quotes (as above) and without, and also with the list enclosed in curly braces - again, no dice.