Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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”
Solved! Go to 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)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Toni_LW
Do you have the both the columns in a single table? Please paste the table as it is:
[Dialog identifier] [Dialog value]
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you!
@Toni_LW Try using this
Proud to be a Super User!
@Toni_LW
Do you need this in Power Query or in Power BI using DAX?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I was trying to complete as a power query when you add a custom column.
User | Count |
---|---|
84 | |
75 | |
73 | |
42 | |
36 |
User | Count |
---|---|
114 | |
56 | |
52 | |
43 | |
42 |