Power Query: Replace Values from Column

Key insights

• Video overview: This YouTube video shows how to replace values in one column using values from another column in Power Query.
  It explains the UI limits and why you often need to edit the query or add a helper column.
• Replace values vs M code: The standard "Replace values" tool works inside a single column and uses fixed values.
  To make replacements depend on other columns you must use or modify the query's M code.
• text columns and Table.ReplaceValue: Power Query treats text columns as string replacements and non-text columns as whole-cell replacements.
  The M function Table.ReplaceValue is the key building block for dynamic, cross-column replacements.
• conditional column: A simple no-code approach is to add a conditional column that chooses the replacement value based on another column.
  Then you can remove or replace the original column with that new, computed column.
• edit generated step and expressions: You can edit the Replace Values step to reference another column using expressions like "each if [Other] = null then [This] else [Other]".
  This keeps transformations compact but requires basic M editing skills.
• best practices: Test for nulls, keep steps readable, and preserve the original step so you can roll back.
  Document the logic in the query and validate results on sample rows before applying to full data.

Video at a glance

The YouTube tutorial by Excel Off The Grid walks viewers through how to replace values in one column based on the contents of another column inside Power Query. The presenter begins with a clear demonstration and timestamps the main sections so viewers can jump to the part they need, including an introduction, a basic replace example, an intermediate “add column shuffle,” and a final cross-column replacement. As a result, the video serves both beginners and intermediate users who want a practical route from the standard UI to a code-based solution.

Importantly, the video stresses that Power Query does not offer a single no-code button to replace values based on another column; instead, the solution relies on built-in steps and occasional tweaks to the generated M code. Therefore, viewers are nudged to understand how the tool generates steps and how to modify them safely. This approach makes the tutorial more than a recipe; it is also an introduction to working confidently with the underlying engine.

Demonstration and techniques shown

First, the presenter shows how the standard Replace values operation works when you want to swap a static value inside a single column. Then, he explains the limitations: the standard UI expects a fixed search and a fixed replacement, so it cannot directly use a different column as the replacement source. Consequently, the video moves to two practical workarounds that most users will recognize—adding a conditional column or editing the generated step to reference another column.

Next, the presenter demonstrates adding a conditional column that encapsulates the replacement rules using values from another field, which is then used to overwrite the original column. This path remains intuitive because it uses the GUI and makes the logic visible in the query steps, which helps teams that prefer low-code solutions. Alternatively, for users comfortable with code, the video shows how to edit the step produced by a Replace operation so the replacement expression reads values from a neighboring column instead of a static literal.

Finally, the video highlights the subtle difference in behavior depending on data type: text columns often trigger instance-level replacement while non-text columns typically replace whole cell contents. As a result, the exact outcome depends on both the column type and the chosen replacement function. The presenter underscores that knowing this difference helps avoid surprises when you switch between text and numeric fields.

M code patterns and examples

Throughout the tutorial, the instructor relies on M code to make replacements dynamic and robust, pointing to the central role of Table.ReplaceValue for advanced scenarios. Rather than showing copy-paste magic, the video explains how the replacement can be driven by an expression that returns the new value per row, which lets one column inform updates to another. For instance, the replacement expression can fall back to the original value when the helper column is null, thereby preserving data when no change is required.

Furthermore, the presenter clarifies how the Replacer.ReplaceValue behavior interacts with text and non-text types, and why you might choose a conditional expression over a simple replace. This helps users craft rules like “if the helper column is blank then keep the original, otherwise use the helper value,” which improves safety. Consequently, the shared patterns let users implement dynamic replacements without losing the ability to audit or revert changes.

Tradeoffs and challenges

Choosing between a GUI-based conditional column and direct M edits involves tradeoffs in readability, maintainability, and performance. While the conditional column is easier to read and understand for teams that avoid code, it can create extra steps and intermediate columns that clutter a query. Conversely, editing the generated replace step produces a compact solution but increases the chance of subtle errors if the expression does not handle nulls, types, or unexpected values.

Performance is another consideration: row-by-row expressions can be slower on very large tables, so users must balance clarity with speed. Additionally, the use of dynamic replacements means that schema or data type changes can break a previously working step, which makes testing and version control more important. Thus, the video recommends cautious editing and keeping backup steps so you can revert if a replacement behaves differently after a source change.

Finally, collaboration and handover create challenges because peers unfamiliar with M code may struggle to maintain bespoke expressions. Therefore, teams must weigh the benefits of a lean M solution against the need for transparency and documentation. In practice, documenting the intent of each critical step and using descriptive query names reduces the risk and smooths future maintenance.

Practical advice for users

For most users, the simplest route is to start with the GUI by adding a clear conditional column and then remove the original if the results look correct, because this approach is safe and auditable. For power users, the video suggests gradually learning how the generated steps map to Table.ReplaceValue patterns so you can replace intermediate columns with a single expressive step. In either case, testing on a copy of the data and handling nulls explicitly will prevent accidental data loss.

In conclusion, the Excel Off The Grid video gives a pragmatic path from the standard Replace values command to dynamic cross-column replacements by combining GUI steps with targeted M edits. Moreover, it teaches viewers to recognize the tradeoffs and to choose the approach that best fits their team’s skills and the dataset’s scale. As a result, the tutorial is a useful resource for anyone who needs controlled, repeatable replacements that depend on other columns.

Keywords

Replace values based on another column Power Query, Power Query replace values from other column, Conditional replace Power Query, Power Query lookup and replace values, Replace column values using another column Power Query, Power Query if then replace values, Dynamic value replacement Power Query, Power Query merge table replace values