What is the code for a multiple condition, nested IF statement?

[jk8979356]

I need to create a multiple condition nested multiple IF statement to identify matches and then add text in a new conditional column. Their are two columns: [Column.Family] [Column.Item] and the logic is:

IF [Column.Family] equals "Blue" or "Black" or "White" or "Red" AND
IF [Column.Item] starts with "Mens Jeans" then return "Mens" in the new conditional column, else return [Column.Item]

IF [Column.Family] equals "Blue" or "Black" or "White" or "Red" AND
IF [Column.Item] starts with "Womens Shirts" then return "Womens" in the new conditional column, else return [Column.Item]

IF [Column.Family] equals "Blue" or "Black" or "White" or "Red" AND
IF [Column.Item] starts with "Youth Boys Shoes" then return "Youth Boys" in the new conditional column, else return [Column.Item]

IF [Column.Family] equals "Small" or "Medium" or "Large" or "XL" AND
IF [Column.Item] starts with "Football Uniforms" then return "Football" in the new conditional column, else return [Column.Item]

How can I create the multiple conditions and multiple nested if statements in M?

[v-yingjl · Accepted solution]

Hi @jk8979356 ,

You can add a custom column in power query:

let
    Source = ...,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Family", type text}, {"Item", type text}}),
    #"Added Custom" = 
        Table.AddColumn(
            #"Changed Type", "Custom",
            each if ([Family] = "Blue" or [Family] = "Black" or [Family] = "White" or [Family] = "Red") and Text.Start([Item],10) = "Men Jeans" 
                then "Men" 
            else if 
                    ([Family] = "Blue" or [Family] = "Black" or [Family] = "White" or [Family] = "Red") and Text.Start([Item],13) = "Womens Shirts" 
                then "Womens" 
            else if 
                    ([Family] = "Blue" or [Family] = "Black" or [Family] = "White" or [Family] = "Red") and Text.Start([Item],16) = "Youth Boys Shoes" 
                then "Youth Boys" 
            else if 
                    ([Family] = "Small" or [Family] = "Medium" or [Family] = "Large" or [Family] = "XL") and Text.Start([Item],17) = "Football Uniforms" 
                then "Football"
            else [Item])
in
    #"Added Custom"

Attached a sample file in the below, hopes to help you.

In addition, here are some articles about nested if statements in power query that you can refer:

1. IF Functions in Power Query Including Nested IFS 
2. Logical Operators and Nested IFs in Power BI / Power Query 
3. Power Query if Statements 

Best Regards,
Yingjie Li

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

[rogletree]

I think this is what you want:

Column =

if ([Column.Family] = "Blue" or [Column.Family] = "Black" or [Column.Family] = "White" or [Column.Family] = "Red" then if (Text.Start([Column.Item], 10) = "Mens Jeans" then "Mens" else if Text.Start([Column.Item], 13) = "Womens Shirts" then "Womens" else if Text.Start([Column.Item], 16) = "Youth Boys Shoes" then "Youth Boys"))

else if ([Column.Family] = "Small" or [Column.Family] = "Medium" or [Column.Family] = "Large" or [Column.Family] = "XL" then if (Text.Start([Column.Item], 17) = "Football Uniforms" then "Football"))

else [Column.Item]

There may be a better way to do it but if I'm understanding correctly what you want then that should work. I may have missed a parenthesis or bracket somewhere.