Forum Discussion
Power Query to replace multiple columns with different values based on another column value
Current data
Greg, thank you for quick. I a beginner to power bi so I will try to explain to best as I can. I want to be able search text like "Delivery" from column (Condition Reason) and replace those other columns highlighted in yellow with different values as shown.
I can get UNIT column to change as follow:
Table.ReplaceValue(#"Changed Type", each [UNIT], each if Text.Contains([Condition Reason], "Delivery") then "SelfService" else [UNIT],Replacer.ReplaceText, {"UNIT"}),
But when I try to have multiple replace value, it will only replace the last value in M code.
This is the final result I am trying to achieve.
Any suggestion?
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"})