Forum Discussion
I need help to create a custom column using an IF statement based on two columns
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”
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)
7 Replies
I was trying to complete as a power query when you add a custom column.
- Both of the columns are in a single table.
- Fowmy
Super User
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)
