Replace value with value from another column?

[Dan80]

Hi all,
I quite often have the scenario in power query (particularly when merging data using a full outer join) where I need to replace a value with a value contained in a seperate column. Is there a way of doing this within 'replace values'? Currently I just create another column and use an if statement which works perfectly well but wonder whether my queries would run more efficiently if I could use a more 'powerful' replace value function? Thanks. Dan

[MattAllington · Accepted solution]

 I don't know the answer but let me give you a tip on how I go about discovering solutions to problems like this.   Firstly I make sure the formula bar is turned on.   Then I perform steps that I want to use as a starting position.   In this case perform the replacer Step  and look at the code that is generated.   Then add the custom column as you've done before and look at that code too.   Finally see if you can work out how to take the column reference from the second step and insert it in the first step. Of course you may need to delete these steps that you created and do it again.  The whole point is that the language is reasonably easy to deconstruct if you do it this way and learn ho wit works. 

[v-ljerr-msft]

Hi @Dan80,

Have you tried the solution provided above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[Daniil · Accepted solution]

Yes, you can use Replace Values for this, and Imke Feldmann explains how here: http://www.thebiccountant.com/2017/07/23/transforming-a-column-with-values-from-another-column-in-po...

[Dan80]

Thanks all for your replies - great as always!  Imke is amazing and her solution has worked for me.  Thanks again

[MattAllington · Accepted solution]

 I don't know the answer but let me give you a tip on how I go about discovering solutions to problems like this.   Firstly I make sure the formula bar is turned on.   Then I perform steps that I want to use as a starting position.   In this case perform the replacer Step  and look at the code that is generated.   Then add the custom column as you've done before and look at that code too.   Finally see if you can work out how to take the column reference from the second step and insert it in the first step. Of course you may need to delete these steps that you created and do it again.  The whole point is that the language is reasonably easy to deconstruct if you do it this way and learn ho wit works.