Use List.Contains. If you refer to a single column from a table, that is a list, you can use List functions on that column:

= Table.AddColumn(#"Added Custom2", "Custom", each if List.Contains([Grouped][User Group ID],"UserDomain users") then true else false)

Hi Catalin Sir,

Table.AddColumn("[Count]", "ED", each if List.Contains([Count][User Group ID], "Everyone") then true else false

Got error,  my syntax correct?

and how to incorporate the OR,  that is the following conditions fulfilled

"Everyone"

"UserDomain users"

"Everyone" and "UserDomain users"

Thank You

Hi Chris,

When you click the button to add new column, in the dialog window that shows up you should paste only the formula, not the entire step syntax. The entire step syntax can be used only if you edit the M code. The formula for adding this new column should be:

=if List.Contains([Count][User Group ID], "Everyone") then true else false

See the List functions description, there is a List.Contains function, List.ContainsAny, List.ContainsAll.

List.ContainsAny can be used in an OR scenario, List.ContainsAll can be used in a AND scenario.

Hi Catalin,

my current ED 

Table.AddColumn(#"Added Custom2", "ED", each if List.ContainsAny([Grouped][User Group ID], {"Everyone","UserDomain users"}) then "Yes" else "No")

123,  345 and 998 should be "No"

realised that only equal to "Yes" if

"Everyone","UserDomain users" and all the rest null is equal

"UserDomain users" and all the rest null is equal

"Everyone" and all the rest null is equal

to "Yes"

Catalin,

Need your expertise to advise on the code to take care null

Chris Yap said
and how to incorporate the OR,  that is the following conditions fulfilled
"Everyone"
"UserDomain users"
"Everyone" and "UserDomain users"
 
  

I think the query returns what you described above, not sure I understand what you mean.

From those User ID's, which one should return No and which should return Yes?

Hi Catalin,

123,  345 and 998 should return "No",  basically only

"Everyone" + the rest null
"UserDomain users"  + the rest null
"Everyone" and "UserDomain users"  + the rest null

return "Yes"

How to code it in the ListContainAll or ListcontainAny ?

Thank you !

Ok, I understand that 998 should return no, but why 898 should return yes?

Add a new column with this formula:

898 is return No

Catalin,   thank you so much on your expertise in PQ,   really appreciate it 

Catalin

are you able to explain your formula as layman as possible

Thank you very much

Chris Yap said
123,  345 and 998 should return "No",  basically only

Chris Yap said
898 is return No  

898 returns no, but it was not in the list you mentioned that should return no.

1. Left side of the equation: formula:  List.Count([Grouped][User Group ID]) will return a number that represents how many values are in the User Group ID column (excludes null values from that column).

2. Right side of the equation: formula: List.Select([Grouped][User Group ID], each List.Contains({"Everyone","UserDomain users"}, _)) will return another list with only those entries from [User Group ID] that can be found in this list: {"Everyone","UserDomain users"}, obviously there can be between 0 or 2 items in this list produced, if there is no match or one or both values are found.

If you count the items from the list produced with this last formula, you will be able to compare it with the full list count. If the count is the same, then your conditions are met.

if List.Count([Grouped][User Group ID]) = List.Count(List.Select([Grouped][User Group ID], each List.Contains({"Everyone","UserDomain users"}, _))) then "Yes" else "No"

Hi Catalin,

I get what you mean,   898 got no "Everyone","UserDomain user" at all,  this is not true in real environment,  I didn't mock the data accurately  haha

Thank you !