Forum Discussion
replacing values based on another column using M
Hi all, I'm trying to replace some incorrect values in column A based on the values in column B. Essentially, I would like to change all of the values in Column A to "127" IF Column B = "table" without adding a new column.
I've tried to use the Replacer.ReplaceText and Table.ReplaceValue functions but can't seem to get it right. Appreciate the help
Current table
| Column A | Column B |
| 124 | table |
| 124 | table |
| 125 | table |
Desired table
| Column A | Column B |
| 127 | table |
| 127 | table |
| 127 | table |
Using → https://www.biinsight.com/quick-tips-conditionally-replace-values-based-on-other-values-in-power-query/ as an example
Table.ReplaceValue(#"Changed Type", each [Column A], each if [Column B] = "table" then 127 else [Column A], Replacer.ReplaceValue,{"Column A"})
3 Replies
= Table.AddColumn(#"Changed Type", "Custom", each if [Column B] = "table" then Replacer.ReplaceValue([Column A],[Column A],127) else [Column A])Hey ChrisMendoza, I appreciate the help here. However I'd like to find out a solution that does not require adding a new column. Thanks!
Using → https://www.biinsight.com/quick-tips-conditionally-replace-values-based-on-other-values-in-power-query/ as an example
Table.ReplaceValue(#"Changed Type", each [Column A], each if [Column B] = "table" then 127 else [Column A], Replacer.ReplaceValue,{"Column A"})