



August 21, 2019

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"
"User\Domain users"
"Everyone" and "User\Domain users"
Thank You


November 8, 2013

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.


August 21, 2019

Hi Catalin,
my current ED
Table.AddColumn(#"Added Custom2", "ED", each if List.ContainsAny([Grouped][User Group ID], {"Everyone","User\Domain users"}) then "Yes" else "No")
123, 345 and 998 should be "No"
realised that only equal to "Yes" if
"Everyone","User\Domain users" and all the rest null is equal
"User\Domain users" and all the rest null is equal
"Everyone" and all the rest null is equal
to "Yes"


November 8, 2013

Chris Yap said
and how to incorporate the OR, that is the following conditions fulfilled
"Everyone"
"User\Domain users"
"Everyone" and "User\Domain 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?
123 |
345 |
678 |
898 |
998 |
1190 |
1298 |


November 8, 2013

Ok, I understand that 998 should return no, but why 898 should return yes?
Add a new column with this formula:
if List.Count([Grouped][User Group ID]) = List.Count(List.Select([Grouped][User Group ID], each List.Contains({"Everyone","User\Domain users"}, _))) then "Yes" else "No"


November 8, 2013

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","User\Domain users"}, _)) will return another list with only those entries from [User Group ID] that can be found in this list: {"Everyone","User\Domain 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","User\Domain users"}, _))) then "Yes" else "No"
1 Guest(s)
