← All posts
10 July 2026Power BI

Conditional Replace in Power Query M: Replace Values Based on Another Column

The Replace Values button in Power Query is all-or-nothing. Here's the one-step Table.ReplaceValue trick that lets you replace a value only when another column meets a condition — no conditional column, no delete, no rename.

The Replace Values button in Power Query has one big limitation: it's all-or-nothing. It replaces every occurrence of a value in a column, with no way to say "only when this other column says so".

Say you've got a table like this:

CategoryTypeSub-TypeValueDiscount DayRetailSale120StandardRetailSale340Discount DayOnlineSale85StandardOnlineReturn-40

You need Sub-Type to say "Discount" instead of "Sale" — but only where Category = "Discount Day". The row where Category is "Standard" should keep its "Sale" value untouched.

The workaround everyone does first

The instinctive fix is a three-step shuffle:

  1. Add a conditional column: if [Category] = "Discount Day" and [Sub-Type] = "Sale" then "Discount" else [Sub-Type]

  2. Delete the original Sub-Type column

  3. Rename the new column back to Sub-Type

It works, but it's three applied steps, your column ends up in a different position, and the query pane fills up with Added Custom / Removed Columns / Renamed Columns noise for what is conceptually one operation.

The one-step trick: Table.ReplaceValue with functions

Here's the part the UI never tells you: the second and third arguments of Table.ReplaceValue — the "old value" and "new value" — don't have to be literals. They can be each functions, evaluated per row, with access to every column in that row.

= Table.ReplaceValue(
    #"Previous Step",
    each [Sub-Type],
    each if [Category] = "Discount Day" and [Sub-Type] = "Sale"
         then "Discount"
         else [Sub-Type],
    Replacer.ReplaceValue,
    {"Sub-Type"}
)

One step. No helper column, no reordering, no rename. The easiest way to write it is to do a normal Replace Values through the UI first (replace "Sale" with "Discount"), then edit the generated step in the formula bar — swap the two literal values for the each expressions above.

How it actually works

The five arguments are:

  1. table — your previous step

  2. oldValue — normally a literal like "Sale". Passing each [Sub-Type] means "the old value is whatever this row currently holds", so the match always succeeds and the decision moves entirely into argument 3.

  3. newValue — the conditional logic. Return the replacement when your condition is met, otherwise return the original [Sub-Type] so untouched rows stay untouched. The else branch is not optional — forget it and every non-matching row errors or nulls out.

  4. replacer — Replacer.ReplaceValue for whole-cell replacement, Replacer.ReplaceText for substring replacement.

  5. columns — the column(s) to write into, as a list.

Because both functions receive the full row record, you can reference any column — [Category], [Type], [Value], whatever — even though you're only writing to Sub-Type. That's the whole trick.

Variation: conditional partial text replace

If you only want to swap a fragment of the text rather than the whole cell, keep the literals but make them conditional, and use Replacer.ReplaceText:

= Table.ReplaceValue(
    #"Previous Step",
    each if [Category] = "Discount Day" then "Sale" else "",
    "Discount",
    Replacer.ReplaceText,
    {"Sub-Type"}
)

Here the old value is what's conditional: on matching rows we search for "Sale"; on everything else we search for "", which Replacer.ReplaceText treats as "replace nothing". So "Sale - Flash" becomes "Discount - Flash", but only on Discount Day rows.

Variation: multiple mappings at once

Once you're inside an each, the logic can be as involved as you like — chained conditions, or a lookup against a record acting as a mapping table:

= Table.ReplaceValue(
    #"Previous Step",
    each [Sub-Type],
    each if [Category] <> "Discount Day" then [Sub-Type]
         else Record.FieldOrDefault(
             [ Sale = "Discount", Return = "Refund" ],
             [Sub-Type],
             [Sub-Type]
         ),
    Replacer.ReplaceValue,
    {"Sub-Type"}
)

Record.FieldOrDefault looks the current value up in the record and falls back to the original if there's no mapping — so one step handles Sale→Discount and Return→Refund, but only on Discount Day rows.

Gotchas

  • Types must match. If the target column is typed as text, both branches of your if must return text. Mixing text and numbers gives you Expression.Error: We cannot convert... at refresh time.

  • Nulls don't compare quietly. If [Category] can be null, guard it: if [Category] = "Discount Day" is fine (null just evaluates false in an equality check), but anything using Text.Contains([Category], ...) on a null will error. Wrap with [Category] <> null and ... when in doubt.

  • ReplaceValue vs ReplaceText. Replacer.ReplaceValue matches the whole cell; Replacer.ReplaceText matches substrings and only works on text. Using ReplaceText when you meant ReplaceValue is how "Sale" inside "Wholesale" gets mangled.

  • Case sensitivity. M comparisons are case-sensitive. "discount day" won't match "Discount Day". Normalise with Text.Lower([Category]) = "discount day" if the source is inconsistent.

  • Folding. Against a SQL source, a plain literal Replace Values folds; the each-function version usually breaks folding. Fine for small/medium tables, but if you're on a large database table, do the transformation upstream or accept the fold break consciously.

TL;DR

Table.ReplaceValue accepts each functions for its old-value and new-value arguments, and those functions see the entire row. Pass each [YourColumn] as the old value, put your cross-column condition in the new value with the original value as the else, and you've got a conditional replace in a single applied step.

Comments

Loading…