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:
#"Replaced A" = Table.ReplaceValue(#"Changed Type", each [A], each if Text.Contains([D], "Value1") then "SomeNew1" else [A],Replacer.ReplaceText, {"A"}),
#"Replaced B" = Table.ReplaceValue(#"Changed Type", each [B], each if Text.Contains([D], "Value1") then "SomeNew2" else [B],Replacer.ReplaceText, {"B"})
#"Replaced C" = Table.ReplaceValue(#"Changed Type", each [C], each if Text.Contains([D], "Value1") then "SomeNew3" else [C],Replacer.ReplaceText, {"C"})
How can I combined all 3 replace statement into one since each individual replace can't work. When I enter all 3 replace conditions, only the last one works.
4 Replies
Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.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.
