Forum Discussion
Power Query to replace multiple columns with different values based on another column value
Power Query to replace multiple columns with different values based on another column value. I am only able to execute one replace value in power query. I have these two examples below: #"Repla...
Anonymous Please paste sample data as text so that can copy and paste easily to test out different methods.
This is current data that I am trying to convert.
| Status | Condition Reason | Unit | Dept |
| UNKNOWN | Troubleshooting:NewTask::DeliveryOrderSuccess | DediccatedService | HR |
| UNKNOWN | Troubleshooting: Request-Agent-DeliveryGateway::DeliveryGateway | DediccatedService | Finance |
| UNKNOWN | Troubleshooting:NewTask::DeliveryOrderSuccess | SelfService | Finance |
| UNKNOWN | Troubleshooting:NewTask::DeliveryOrderSuccess | SelfService | Finance |
| UNKNOWN | Troubleshooting:NewTask::Deliverylneligible | UNKNOWN | Finance |
| UNKNOWN | Troubleshooting:NewTask::DeliveryOrderSuccess | SelfService | HR |
| UNKNOWN | Troubleshooting:NewTask::DeliveryOrderSuccess | SelfService | Finance |
| UNKNOWN | Troubleshooting:NewTask::DeliveryOrderSuccess | SelfService | Finance |
| UNKNOWN | Troubleshooting: Request-Agent-DeliveryGateway::DeliveryGateway | SelfService | Finance |
I want to convert the data into this based on search criteria on column (Condition Reason)
| Status | Condition Reason | Unit | Dept |
| Success | Troubleshooting:NewTask::DeliveryOrderSuccess | SelfService | TechSupport |
| Failed | Troubleshooting: Request-Agent-DeliveryGateway::DeliveryGateway | SelfService | TechSupport |
| Success | Troubleshooting:NewTask::DeliveryOrderSuccess | SelfService | TechSupport |
| Success | Troubleshooting:NewTask::DeliveryOrderSuccess | SelfService | TechSupport |
| Failed | Troubleshooting:NewTask::Deliverylneligible | SelfService | TechSupport |
| Success | Troubleshooting:NewTask::DeliveryOrderSuccess | SelfService | TechSupport |
| Success | Troubleshooting:NewTask::DeliveryOrderSuccess | SelfService | TechSupport |
| Success | Troubleshooting:NewTask::DeliveryOrderSuccess | SelfService | TechSupport |
| Failed | Troubleshooting: Request-Agent-DeliveryGateway::DeliveryGateway | SelfService | TechSupport |
I have tried this but it only allow me to convert the last one only.
Replaced Unit" = Table.ReplaceValue(#"Changed Type1", each [Unit], each if Text.Contains([Condition Reason], "DropShip") then "SelfService" else [Unit],Replacer.ReplaceText, {"Unit"})
Replaced Dept" = Table.ReplaceValue(#"Changed Type1", each [Dept], each if Text.Contains([Condition Reason], "DropShip") then "TechSupport" else [Dept],Replacer.ReplaceText, {"Dept"})