Power Query Replace Value Not Working? Find the Fix

When Power Query’s Replace Values command appears not to work, the cause is usually a mismatch in the value, data type, selected column, or step order. In Excel for Microsoft 365 on Windows, open the query, select the intended column, choose Transform > Replace Values, enter the value to find and its replacement, and then refresh or load the query to check the result.

What Replace Values Does in Power Query

Power Query does not change the source worksheet directly. It records transformation steps in a query and applies those steps when the preview is evaluated or the query is refreshed. A replacement can therefore look successful in the Editor but not appear in the sheet if you are looking at a different query output, have not refreshed, or are checking the source table instead of the loaded result.

The command operates on the selected column. In the Power Query Editor, select a column and use Transform > Replace Values, or right-click the column heading and choose Replace Values. The resulting operation becomes an Applied Step, usually visible in the Query Settings pane. Selecting a different column before creating the step is a common reason the expected cells remain unchanged.

For text, it is also important to know whether you mean to replace a whole cell value or a piece of text inside it. For example, changing a cell containing exactly Pending to Open is different from changing the word Pending inside Order Pending. The replacement mode and options determine which result you get.

Check the Column, Value, and Match Mode

Start by confirming what Power Query actually sees in the preview, rather than relying only on how a cell looks in Excel. A value displayed as a date, number, or trimmed-looking label may have a different underlying type or contain invisible characters. Also check the column heading in the selected step: replacement steps target named columns, and renaming or removing a column can make a step fail or leave the intended column untouched.

In the Replace Values dialog, inspect both entries carefully. A leading space in the search value, a different punctuation mark, or a copied nonbreaking space can make a search miss. If the dialog shows an option called Match entire cell contents, its setting matters: enabled, it looks for a whole-cell match; disabled, text replacement can target text within a cell. Availability and presentation of this option can vary by Power Query host and operation.

To replace the complete value CA with California, use a whole-value match if that is the intended behavior.
To change CA inside CA - West, use a text replacement that does not require a whole-cell match.
Before replacing a short string such as 1, consider whether it could also occur inside values such as 10 or 21.

Text matching can be case-sensitive in Power Query’s M text operations. A search for pending may not match Pending in a case-sensitive operation. If capitalization varies, normalize it first or use a transformation designed to handle the variations, then verify the preview. Do not assume a replacement that succeeds for one capitalization will cover every spelling.

Look at the Data Type and Applied Step Order

Power Query assigns data types to columns, often through an automatically added Changed Type step. A column that looks like text might already be typed as a number, date, or logical value. Searching for the text 5 will not necessarily match the numeric value 5, and the word null is not the same thing as Power Query’s null value. Check the type icon beside the column name and review the Applied Steps list.

Step order matters because each step receives the output of the step before it. If a replacement is made after a type conversion, it sees the converted values; if a later step changes the column again, the displayed result may differ from what you expected. For example, a text replacement intended to change 0017 will not work as expected if the value was converted to the number 17 first. Once leading zeros are discarded, converting 17 back to text cannot reconstruct them.

To diagnose this, click each Applied Step in order and watch the preview. If the target value disappears or changes type before the replacement step, move the replacement earlier or adjust the type conversion. You can often right-click a step and select Move Up or Move Down, but check the resulting preview because moving steps can alter later operations. For identifiers that must preserve zeros, set the column to Text before a numeric conversion can remove them.

Handle Nulls, Blanks, Errors, and Hidden Characters

Several different things can look like an empty or troublesome cell. Power Query distinguishes a null value from an empty text string, written as "", and from a cell containing spaces. A formula that returns an empty string may not be replaced by a search for null. Likewise, the text NULL imported from a file is ordinary text unless you explicitly transform it.

Errors are different again: they are not values that ordinary Replace Values necessarily matches. To substitute for errors, select the relevant column and use Transform > Replace Errors. This creates an error-handling step rather than a standard text replacement. Use it deliberately; replacing every error with a blank or default can hide upstream problems such as invalid dates or malformed numbers.

Invisible characters are another frequent source of misses. A copied label might contain a nonbreaking space, a tab, or a line break, even though it looks like a normal word. Select the column and try Transform > Format > Trim to remove leading and trailing spaces, or Transform > Format > Clean to remove many nonprinting characters. These operations do not guarantee removal of every unusual Unicode character. For a nonbreaking space, an explicit replacement may be needed; in M, Character.FromNumber(160) represents that character. Inspect a sample value and test cleanup on a small set before applying it broadly.

Use the Correct Replacement for Text or Numbers

Power Query’s M language records the details of a replacement in a step. A text-substring replacement commonly uses Replacer.ReplaceText, while a whole-value replacement commonly uses Replacer.ReplaceValue. For instance, a step like Table.ReplaceValue(Source, "old", "new", Replacer.ReplaceText, {"Column"}) replaces occurrences of the text old in the named column. The exact step generated by the interface can depend on the operation and data.

For a numeric column, use numeric values rather than quoted text when editing M. For example, the conceptual old and new values should be 0 and 1, not "0" and "1", if the column is typed as a number. In the Editor, check the preview and the column type after replacement. If you edit a formula in the formula bar or Advanced Editor, preserve the commas, braces, column name, and step reference in the existing expression. A syntactically valid edit can still target the wrong step or column.

It is usually safer to create a replacement through the interface first, then inspect the generated step, than to write a full M expression from scratch. This also makes the transformation visible to someone reviewing the query later. If a search needs complicated conditional logic—for example, replacing different terms depending on another column—a custom column or a carefully designed transformation may be clearer than repeated Replace Values steps.

Confirm Refresh and the Loaded Output

After the preview is correct, the replacement still needs to reach the workbook output. In the Power Query Editor, use Home > Close & Load to return the query results to Excel, or Home > Close & Load To if you need to choose a destination or loading option. For a query already loaded to a worksheet, use Data > Refresh All in Excel to refresh workbook connections and queries. A refresh can also be started from the query’s context menu in the Queries & Connections pane.

Check that you are inspecting the table or connection produced by the query you edited. Workbooks can contain several queries with similar names, and one query may reference another. If a downstream query applies its own steps, changing an upstream query does not guarantee that the final output will display the replacement exactly as expected. Follow the query chain and inspect the final loaded result.

Excel for the web may let you view or refresh some workbook queries, but query editing support and available connectors can differ from the Windows desktop. If the Editor or a particular transformation is unavailable in your environment, make the change in Excel for Microsoft 365 on Windows and then save and refresh the workbook as appropriate.

A Reliable Troubleshooting Sequence

Rather than repeatedly trying different search strings, use a short diagnostic sequence. It isolates whether the issue is in the source value, the transformation, or the final output.

Open the intended query from Data > Queries & Connections and confirm its preview shows the row you want to change.
Select the correct column and note its data type. Check whether the target is text, a number, null, an empty string, or an error.
Click through Applied Steps to see when the value changes or disappears. Pay particular attention to Changed Type, Trim, Clean, and earlier replacement steps.
Create or edit the replacement with the right whole-value or substring behavior. Check capitalization, spaces, punctuation, and hidden characters.
Review the preview after the replacement and after every following step. A later transformation may undo or obscure the result.
Close and load the query, refresh the output if needed, and verify the final destination rather than only the source data.

If the replacement still fails, make a small test query or duplicate the query and apply the change to a few representative rows. Include one exact match, one near-match, and one value with suspected spacing or capitalization differences. A controlled test helps reveal whether the operation is matching a whole value, a substring, or a different underlying type.

Frequently Asked Questions

Why does Replace Values change one row but not another?
Those cells may not contain identical values even if they look alike. Differences can include capitalization, spaces, nonbreaking spaces, punctuation, or data type. Compare the preview and clean or normalize the values before replacing them.

How do I replace part of a text value in Power Query?
Select the text column and use Replace Values with whole-cell matching turned off if that option appears. Confirm the preview, because replacing a short sequence can also affect larger strings that contain it.

How do I replace null instead of the word null?
Use an actual null value, not the quoted text "null". The quoted form searches for letters in text; the unquoted M value null represents missing data. An empty string is a separate value.

Why does my replacement disappear after refresh?
Power Query replays its steps against the refreshed source. The source may have changed, the replacement may target a different value or type, or a later step may alter the result. Inspect the refreshed preview one Applied Step at a time.

Can I replace errors with a value?
Yes. Use Transform > Replace Errors on the relevant column, rather than treating errors as ordinary text. Choose a replacement carefully, since suppressing errors can conceal problems in the source data or earlier transformations.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.