How to replace value with a value from another column in Power Query

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

In our example, we replace null with “x”
  1. Right-click the column of interest
  2. Replace Values…
  3. Input the value to find, “null” in our example
  4. Input the value to replace with, “x” in our example (any letter would do)
  5. 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. πŸ˜‰

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Power Query and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.