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 
Is there a way to copy or move the value in a single cell to another single cell in an adjacent column?
LikeLike
Check this out!
LikeLike
I don’t think this will work in Power Query. Any other suggestions?
LikeLike
Ooops I didn’t know you refer to Power Query. In Power Query, we can move a column but not a cell. What kind of transformation you try to achieve?
LikeLike
The leftmost column contains a date I would like to retain. Then the column can be deleted. This makes the first column in the query table a ticker symbol which is later used to do a vlookup.
Is there a way to provide a screen shot for better illustration?
LikeLike
Sorry I don’t think you can post a screenshot here…
Without the context, it’s hard to give advice but on the top of mind is to add a conditional column
LikeLike