PQ_Miltiple IF

[AllanBerces]

Hi can anyone help me on my PQ, i want to use multiple if condition on my report. 

if Location is equal to South and Trade is equal to Mech then New Value is 21
if Location is equal to South and Trade is equal to Civil then New Value is 9
if Location is equal to South and Trade is equal to E&I then New Value is 6

if Location is equal to North, East and West and Trade is equal to Mech then New Value is 18
if Location is equal to North, East and West and Trade is equal to Civil then New Value is 10
if Location is equal to North, East and West and Trade is equal to E&I then New Value is 4

DESIRED OUTPUT

Location	Trade	Date	Current	New Value
South	Mech	1/1/2026	0	21
South	Civil	1/5/2026	0	9
South	E&I	1/19/2026	0	6
South	Mech	2/2/2026	0	21
South	Mech	2/9/2026	0	21
North	E&I	2/16/2026	86	4
North	Mech	3/2/2026	101	18
North	Mech	3/9/2026	110	18
North	E&I	3/16/2026	110	4
East	Mech	3/30/2026	110	18
East	Civil	4/6/2026	110	10
East	E&I	4/20/2026	110	4
East	Mech	5/4/2026	134	18
West	E&I	5/11/2026	134	4
West	Mech	5/25/2026	134	18
West	Mech	6/1/2026	134	18
West	E&I	6/8/2026	134	4
West	Mech	6/22/2026	110	18
West	Mech	6/29/2026	110	18
West	E&I	7/6/2026	120	4

[cengizhanarslan · Accepted solution]

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

[BA_Pete · Accepted solution]

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

[vojtechsima · Accepted solution]

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

[AllanBerces]

Hi @vojtechsima @cengizhanarslan @BA_Pete thnak you you very much working as i need

[BA_Pete · Accepted solution]

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

[cengizhanarslan · Accepted solution]

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

[AllanBerces]

Hi @BA_Pete @cengizhanarslan thank you for the reply but ir show error

[cengizhanarslan]

You should add this to your current m expression in a correct way. If you share your whole code I can share you the corrected query.

[AllanBerces]

Hi @cengizhanarslan @BA_Pete thnak you for the reply but when i try this part all are ok

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

but when i try this part show error

Thank you