It is a common task to replace a value with another value in Power Query. It can be easily done with “Replace Values”. However if you want to replace a value with the corresponding value in another column, it’s not that straight-forward in Power Query. Most of the time, we would do it by adding a conditional column, followed by removing the original column. It works, but it requires more steps.
Indeed, we could achieve this by using Replace Values in Power Query, followed by a simple modification to the formula generated.
Let’s watch it in action:
Want step-by-step instruction? Please continue to read.
First, replace the value with any value as usual
- Right-click the column of interest
- Replace Values…
- Input the value to find, “null” in our example
- Input the value to replace with, “x” in our example (any letter would do)
- OK
Pay attention to the formula auto-generated.
In plain English, the formula tells Power Query to replace null with “x” in the column “SiteID_new” that came from the step “Source”.
Second, modify the formula
With just a simple modification of the highlighted portion in the formula, we will get what we want – the corresponding value in another column.
Note: Don't miss the keyword each Tip: Mind the spelling. Power Query is case-sensitive
As simple as this. 😉
wmfexcel 