Excel Power Query Value Replacement: How to Use It

How-to basics · Updated November 21, 2026

To replace a value in Excel Power Query, open the query in Power Query Editor, select the relevant column, and choose Transform > Replace Values. Enter the value to find and its replacement, then select Close & Load to return the transformed results to Excel.

What value replacement does in Power Query

Power Query is Excel’s data-import and transformation tool. A replacement is a recorded transformation step in a query, not a direct edit to the original workbook, CSV file, database, or other source. When the query is refreshed, Power Query reads the source again and applies the saved replacement step as part of the refresh.

This makes replacement useful for standardizing recurring data. For example, a monthly sales file might label the same region as West, WEST, and W. You can replace an unwanted label with the agreed spelling before loading the cleaned data. A later refresh applies that rule to new rows too, provided the relevant column is still present and compatible.

Replacement is not the same as Find and Replace on a worksheet. Worksheet Find and Replace edits cells in the current sheet; Power Query records a repeatable instruction in the query. If you change the source data, the query output can be rebuilt from the source and the saved steps.

Replace a value in Excel for Microsoft 365 on Windows

For an existing query, use these steps in Excel for Microsoft 365 on Windows:

  1. Open the workbook and choose Data > Queries & Connections if the query list is not already visible.
  2. Right-click the query and choose Edit to open Power Query Editor. You can also select a query and use the available edit command.
  3. Select the column where the value occurs. Replacement applies to the selected column or columns, so check the selection before continuing.
  4. Choose Transform > Replace Values. Depending on the editor layout, the command may also be available from a column’s right-click menu.
  5. Enter the existing value in Value to Find and the desired value in Replace With. Confirm the dialog.
  6. Review the preview and the new entry in Applied Steps. Choose Home > Close & Load when the result is right.

For example, suppose a Status column contains the text Pending review and you want it to read Pending. Select Status, find Pending review, and replace it with Pending. If the same phrase occurs in a different column, it will not be changed unless that column is selected too.

On Mac, Power Query support and available connectors can vary by Excel build. If Power Query Editor is available for the workbook, the transformation works the same way once you reach the editor, although ribbon placement can differ. Excel for the web does not provide the same full query-editing experience as Excel desktop, so use the desktop app to create or edit this step.

Replace whole values or text within a cell

A key detail is whether you intend to replace a complete cell value or only a matching piece of text. If a cell contains North District and you replace North with N, a text replacement can produce N District. If the aim is to change only a cell whose entire value is North, use a whole-cell match when that option is available in the dialog.

The distinction matters for codes and labels. Replacing 12 inside a text field could affect values such as A12B as well as a cell containing only 12. Review the dialog’s advanced options, including any setting for matching the entire cell contents, when available. Then check the preview for both intended matches and values that should remain unchanged.

Power Query also distinguishes data types. The number 12 and the text value 12 may look identical in a preview, but they are not necessarily the same value. If a replacement does not find what you expect, check the column’s type in the header and the applied type-conversion steps. Choose a replacement value that is appropriate for that type.

Handle blanks, nulls, and errors correctly

Blank-looking cells can represent different things. A null means no value is present; an empty text value is a text string with no characters. A cell containing one or more spaces is different again. These values can look alike in a preview, but a rule aimed at one may not affect the others.

When a replacement seems to miss blank entries, inspect the column and its source rather than assuming all blanks are equivalent. If the source uses empty strings or spaces, clean those deliberately; trimming whitespace with a suitable text-cleaning step may be necessary. Be cautious about converting nulls to a label such as Unknown, because that changes missing-data meaning and can affect later calculations or filters.

Errors are not ordinary values. To replace error cells, select the column and use Transform > Replace Errors, then specify a substitute such as null or a clear text label when appropriate. Replacing an error can hide an underlying problem, such as a failed type conversion. If the error signals invalid source data, investigate the earlier step or source instead of simply masking it.

Use conditional replacement when one fixed value is not enough

Replace Values is best when the rule is simple: a known value should always become the same new value. If the replacement depends on another field or on a range of values, use a conditional transformation instead. For instance, you might assign a shipping category based on a numeric weight, or label a record according to its country and status.

In Power Query Editor, Add Column > Conditional Column creates a new column from specified conditions. You can review the results, remove the old column if appropriate, and rename the new one. A conditional column preserves the original while you validate the rule, which is often safer than overwriting information immediately.

For more involved logic, Add Column > Custom Column lets you write an expression in the Power Query M language. This is useful for multi-condition rules, but a simple replacement is easier to maintain when it meets the need. Whatever approach you choose, test boundary cases—for example, values exactly at a threshold and values that are missing.

What the replacement step looks like in M

Power Query stores transformations as M code. You do not need to edit M for routine replacements, but recognizing the pattern can help when checking a query or adapting a rule. A whole-value replacement step can look like this:

= Table.ReplaceValue(#"Changed Type","N/A",null,Replacer.ReplaceValue,{"Status"})

This example assumes the preceding step is named Changed Type and the column is Status. It replaces the whole value N/A with null in that column. The names and values must match the query; copying the example unchanged will not work if your step or column has a different name.

A text-substitution step can use a text replacer instead:

= Table.ReplaceValue(#"Previous Step","Ltd","Limited",Replacer.ReplaceText,{"Company"})

That example assumes a column named Company and a prior step named Previous Step. It illustrates replacing matching text within the selected text column rather than treating the entire cell as one exact value. M is case-sensitive for identifiers and string matching behavior depends on the operation used, so verify the actual output before relying on capitalization variants.

Applied Steps, refreshes, and replacement order

Each replacement appears in the Applied Steps pane. Selecting a step lets you inspect the result at that point in the transformation sequence; the gear icon, when shown, can reopen settings for an editable step. You can delete an unwanted step with its remove control, but deleting a step also removes its effect from all later results.

Order can change the outcome. If you first replace a short text fragment and later replace a longer label, the second rule may no longer find the original text. Likewise, converting a column’s type before replacing a value can affect whether the match is treated as text, a number, or a date. Arrange steps so that each rule receives the data form it expects, and check the preview after changes.

On refresh, the replacement runs against the refreshed source data. It does not guarantee a successful refresh: renamed columns, changed data types, access problems, or a different source layout can break a query. If a refresh fails, inspect the error and the step where it occurs; a replacement rule cannot repair every upstream source or schema change.

Common problems and practical checks

  • No rows changed: Confirm the right column is selected and that the value has the same type and spelling as the source, including punctuation and spaces.
  • Too many values changed: Check whether the operation replaced text within cells rather than matching whole cell contents. Undo the step or remove it in Applied Steps, then repeat with the appropriate matching option.
  • The replacement appears to vanish: A later step may overwrite, filter, or remove the affected data. Select each applied step in turn to find where the result changes.
  • New source rows are not cleaned: Confirm the query is refreshed and that the transformation step occurs before any step that removes those rows or changes the column structure.
  • Numbers or dates do not match: Review the detected data type and regional interpretation. A date imported as text, for example, may need its type corrected before a replacement rule can behave as intended.

For important data, preserve a copy of the source or workbook before making broad cleanup changes. Also retain the original column when the replacement changes meaning or removes detail; comparing original and cleaned values makes quality checks easier.

Frequently Asked Questions

Does Power Query change the original source file?
No. A normal query transformation changes the data Power Query produces, not the original source file. The cleaned output is loaded to Excel or another destination, and the query keeps its instructions for later refreshes.

Can I replace several values in one column?
Yes. Add a replacement step for each value, or use a conditional column or a more advanced M transformation when many rules are involved. With multiple steps, check their order because an earlier replacement can change what a later step finds.

How do I replace a blank with null?
First determine whether the source contains nulls, empty text, or spaces. They are distinct values. Use the appropriate replacement or cleaning step for what is actually in the column, then inspect the preview to confirm that only the intended entries changed.

Can Power Query replace errors with zero?
Yes. Select the column and use Transform > Replace Errors, entering zero if zero is a valid substitute for that data. Before doing so, check why the errors occurred; replacing them may conceal invalid data or a conversion issue.

Will a replacement apply to data added later?
It will apply when the query refreshes, as long as the new data is included in the source and still has the expected column structure. If the source changes its headers, types, or layout, the query may need adjustment.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.