I need help to create a custom column using an IF statement based on two columns

[Toni_LW]

I'm not sure if this the best solution, I may be making it more complicated than it needs to be.

I have two coloumns:

[Dialog identifier]

[Dialog value]

Data example:

[Dialog identifier]

111_Menu_80

123_Menu_80

133_Menu_80

111_Menu_87

123_Menu_87

133_Menu_87

[Dialog value]

1

2

3

noinput

nomatch

If the "Dialog identifier" ends with "80" then:

1 = DEF

2 = STA

noinput = STA

nomatch = STA

If the "Dialog identifier" ends with "87" then:

1 = DEF

2 = DEF

3 = STA

noinput = STA

nomatch = STA

This is what I was trying to do as a custom column but it doesn't work.... I've also tried a SWITCH function but no joy please help

if Text.EndsWith([Dialog identifier]) = “Menu_80” and [Dialog value] = “1” then “Deflection”

else if Text.EndsWith([Dialog identifier]) = “Menu_80” and [Dialog value] = “2” then “Spoke to an Agent”

else if Text.EndsWith([Dialog identifier]) = “Menu_80” and [Dialog value] = “noinput” then “No Input”

else if Text.EndsWith([Dialog identifier]) = “Menu_80” and [Dialog value] = “nomatch” then “No Match”

else if Text.EndsWith([Dialog identifier]) = “Menu_87” and [Dialog value] = “1” then “Deflection”

else if Text.EndsWith([Dialog identifier]) = “Menu_87” and [Dialog value] = “2” then “Deflection”

else if Text.EndsWith([Dialog identifier]) = “Menu_87” and [Dialog value] = “3” then “Spoke to an Agent”

else if Text.EndsWith([Dialog identifier]) = “Menu_87” and [Dialog value] = “noinput” then “No Input”

else if Text.EndsWith([Dialog identifier]) = “Menu_87” and [Dialog value] = “nomatch” then “No Match”

[Fowmy]

@Toni_LW 

Do you have the both the columns in a single table? Please paste the table as it is:

[Dialog identifier] [Dialog value]

[Toni_LW]

Both of the columns are in a single table.

[Fowmy · Accepted solution]

@Toni_LW 

Add a custom column in Power Query with the following code. I entered Null at the end if non of the conditions are met, you can replace it with anything:

= let
    identifier = [Dialog identifier],
    value = [Dialog value],
    menu80Condition = Text.EndsWith(identifier, "Menu_80"),
    menu87Condition = Text.EndsWith(identifier, "Menu_87"),

    result = 
        if menu80Condition and value = "1" then "Deflection"
        else if menu80Condition and value = "2" then "Spoke to an Agent"
        else if menu80Condition and value = "noinput" then "No Input"
        else if menu80Condition and value = "nomatch" then "No Match"
        else if menu87Condition and value = "1" then "Deflection"
        else if menu87Condition and value = "2" then "Deflection"
        else if menu87Condition and value = "3" then "Spoke to an Agent"
        else if menu87Condition and value = "noinput" then "No Input"
        else if menu87Condition and value = "nomatch" then "No Match"
        else null
in
    result)

[Toni_LW]

Thank you!

[VijayP]

@Toni_LW  Try using this

New Column = SWITCH(TRUE(), RIGHT([[Dialog identifier]]],7)="Menu_80" && 'Table'[Dialog value]]]="1","Deflector",RIGHT([[Dialog identifier]]],7)="Menu_80" && 'Table'[Dialog value]]]="2","Spoke to agent", "others")

[Fowmy]

@Toni_LW 

Do you need this in Power Query or in Power BI using DAX?

[Toni_LW]

I was trying to complete as a power query when you add a custom column.