Multiple conditons in conditional column

[newhopepdx]

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.

[Ritaf1983]

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 link 

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

[newhopepdx · Accepted solution]

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}})