PQ_Miltiple IF

In Power Query → Add Column → Custom Column:

if [Location] = "South" and [Trade] = "Mech" then 21
else if [Location] = "South" and [Trade] = "Civil" then 9
else if [Location] = "South" and [Trade] = "E&I" then 6
else if List.Contains({"North","East","West"}, [Location]) and [Trade] = "Mech" then 18
else if List.Contains({"North","East","West"}, [Location]) and [Trade] = "Civil" then 10
else if List.Contains({"North","East","West"}, [Location]) and [Trade] = "E&I" then 4
else null

Hi AllanBerces ,

In a new custom column ([New Value]) use the following code:

if [Location] = "South" and [Trade] = "Mech" then 21
else if [Location] = "South" and [Trade] = "Civil" then 9
else if [Location] = "South" and [Trade] = "E&I" then 6
else if List.Contains({"North", "East", "West"}, [Location]) and [Trade] = "Mech" then 18
else if List.Contains({"North", "East", "West"}, [Location]) and [Trade] = "Civil" then 10
else if List.Contains({"North", "East", "West"}, [Location]) and [Trade] = "E&I" then 4
else null   // This is your escape value if none of the conditions are met

Pete

hey, AllanBerces ,

if you want less repeated alternative code, check this:

    newColumns = 
        let 
            loc_s = "South",
            loc_new = {"North", "East", "West"},
            result = Table.AddColumn(previousStep, "New Value", each 
            let
                map =
                if [Location] = loc_s then [
                    Mech=21,
                    Civil=9,
                    #"E&I"=6
                ] 
                else if List.Contains(loc_new, [Location]) then [
                    Mech=18,
                    Civil=10,
                    #"E&I"=4
                ]
                else null
                in 
                Record.FieldOrDefault(map, [Trade], null)

            , Int64.Type
            )
            
    in
        result

the whole thingy with your input:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZGxCoMwEIbfxVm43CWmOheHDu3SoYN1KEWoUBBa7fM31hgvGrRDLgf5+D/4UxTRuenaRxRHx+reXwgIJEibVZhDGJXxBO3rT/38UQmnMg/Kr50wb4chLeOg9kCrJKAVpYOyAHRqXnMlAeqRTPuhPNLGycmJAvuZhjFnRRRLjHkl8w7sIM5v75YHShFMtNhYsIJZmBkMY15luG1vAspBUk3aSzXPSwDRRxUnXR4la4EW04B/ajWk21bTCQXbW2Dhb1tqd6xnsuWVXw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, Trade = _t, Date = _t, Current = _t, #"User New Value" = _t]),
    changeType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "en-US"),
    previousStep = changeType,

    newColumns = 
        let 
            loc_s = "South",
            loc_new = {"North", "East", "West"},
            result = Table.AddColumn(previousStep, "New Value", each 
            let
                map =
                if [Location] = loc_s then [
                    Mech=21,
                    Civil=9,
                    #"E&I"=6
                ] 
                else if List.Contains(loc_new, [Location]) then [
                    Mech=18,
                    Civil=10,
                    #"E&I"=4
                ]
                else null
                in 
                Record.FieldOrDefault(map, [Trade], null)

            , Int64.Type
            )
            
    in
        result
in
    newColumns