Log In
Register
Home
August 21, 2019
Offline
Hi Catalin
Thank for helping in my previous post
still got one query on custom column ED
ED - If Grp 1 …… Grp X consists of User\Domain, Everyone, then return "Yes", Else "No"
See my attached
Thank you so much from learning PQ from you
November 8, 2013
Offline
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],"User\Domain users") then true else false)
August 21, 2019
Offline
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
Offline
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
Offline
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"
August 21, 2019
Offline
Catalin,
Need your expertise to advise on the code to take care null
November 8, 2013
Offline
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 |
August 21, 2019
Offline
Hi Catalin,
123, 345 and 998 should return "No", basically only
"Everyone" + the rest null
"User\Domain users" + the rest null
"Everyone" and "User\Domain users" + the rest null
return "Yes"
How to code it in the ListContainAll or ListcontainAny ?
Thank you !
November 8, 2013
Offline
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"
August 21, 2019
Offline
898 is return No
August 21, 2019
Offline
Catalin, thank you so much on your expertise in PQ, really appreciate it
August 21, 2019
Offline
Catalin
are you able to explain your formula as layman as possible
Thank you very much
November 8, 2013
Offline
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)
