Forum Discussion
Multiple conditons in conditional column
The desired outcome is to modify an existing Fund table consisting of the first two columns below by adding the third column.
Funds table:
Fund Name Class Subclass
General Inc General General
Misc Inc 1 General Misc
Misc Inc 2 General Misc
Misc Inc 3 General Misc
Other Inc Whatever null
Still other Inc Something null
etc.
Here’s the code I’ve tried for the Subclass column, but it gives all the General class entries a Subclass of “Misc”
= Table.AddColumn(#"Sorted Rows", "Funds", each if [Fund Name] = "General Inc" then "General" else if [Class] = "General" then "Misc" else null)
Seems like an “AND” is needed in the elseif clause (like: " else if [Class] = "General" AND [Fund Name] <> “General Inc” then "Misc") but I haven’t found the correct syntax.
Thanks.
I tried using an "and" clause but it didn't work... the entries with "General Inc" all ended up as "Misc". I thought I had the "and" clause wrong, but the problem (aparently) was a space character in the Fund Name field. When I modified the Remove Duplicates to include a trim of the Fund Name, it worked....
= Table.TransformColumns(#"Removed Duplicates",{{"Fund Name", Text.Trim, type text}})
2 Replies
Hi newhopepdx
If i understood correctly the needed logic than you can use custom column with the syntax :
if [Fund Name]="General Inc" and [Class]="General" then "General"
else if [Fund Name]="Misc Inc" and [Class]="General" then "Misc"
else "null"if not, you can modify it as you need, this is a good "template".
You can download the file and follow my steps from This linkIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Thanks.
I tried using an "and" clause but it didn't work... the entries with "General Inc" all ended up as "Misc". I thought I had the "and" clause wrong, but the problem (aparently) was a space character in the Fund Name field. When I modified the Remove Duplicates to include a trim of the Fund Name, it worked....
= Table.TransformColumns(#"Removed Duplicates",{{"Fund Name", Text.Trim, type text}})
