Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dan80
Helper II
Helper II

Replace value with value from another column?

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
2 ACCEPTED SOLUTIONS

 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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Daniil
Kudo Kingpin
Kudo Kingpin

4 REPLIES 4
v-ljerr-msft
Employee
Employee

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. Smiley Happy

 

Regards

Daniil
Kudo Kingpin
Kudo Kingpin

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...

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

 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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.