<!-- https://learnsql.cpetoday.com/functions/m/table-replacevalue -->
# Table.ReplaceValue

Language: Power Query M
Category: Table: values and fill
Syntax: Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table
Summary: Replaces every occurrence of one value with another, in the columns you list.
Returns: The table with the matching values replaced.
Level: Beginner
Related: Table.ReplaceErrorValues, Table.TransformColumns, Table.FillDown, Table.TransformColumnTypes

## What it does
Table.ReplaceValue finds a value and swaps it for another value, across the columns you name. Bank files often show zero as a dash, and a report may need a label changed from "Acct." to "Account". This step handles both. The replacer decides whether the whole value must match or only part of it.

## Syntax and arguments
- **table**: the table to change. Required.
- **oldValue**: the value to find, such as `"-"` or `null`. Required.
- **newValue**: the value to put in its place, such as `null` or `0`. Required.
- **replacer**: how to match. `Replacer.ReplaceValue` matches the whole value. `Replacer.ReplaceText` matches text inside a value. Required.
- **columnsToSearch**: a list of columns to search, in braces. Required.

## Example
A bank export shows a dash in the Debit and Credit columns where there is no amount. The dash is replaced with a blank, so the columns can be converted to numbers.

```powerquery
let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    NoDashes = Table.ReplaceValue(Source, "-", null, Replacer.ReplaceValue, {"Debit", "Credit"}),
    Typed = Table.TransformColumnTypes(NoDashes, {{"Debit", type number}, {"Credit", type number}})
in
    Typed
```

Only the whole value "-" is replaced. A dash inside a longer description is not touched, because the replacer matches whole values.

## Example: a second, different use
Descriptions use the abbreviation "Acct." inside longer text. Replacer.ReplaceText changes the abbreviation wherever it appears.

```powerquery
let
    Source = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
    Expanded = Table.ReplaceValue(Source, "Acct.", "Account", Replacer.ReplaceText, {"Description"})
in
    Expanded
```

## Things to watch
- Replacer.ReplaceValue needs the whole value to match. Replacer.ReplaceText changes any match inside the text, which can change more than you meant.
- To replace blanks, use null as the old value.
- Name every column you want searched. Columns not in the list are left alone.
- Query folding: this step sometimes folds on database sources and sometimes does not. Check View Native Query.

## In business terms
Cleaning placeholder codes such as dashes, "N/A" and "n/a" before totals is one of the most common preparation steps for an export. Doing it in the query makes the rule the same every month.
