Power Query Replace Text: Steps, Options, and Examples

To replace text in Power Query, open the Power Query Editor, select the column, and choose Transform > Replace Values. Enter the text to find and its replacement, then select OK; the change is recorded as a query step and applied again whenever the query refreshes.

What replacing text means in Power Query

Power Query is Excel’s data-import and transformation tool. Unlike Find and Replace on a worksheet, a Power Query replacement changes the instructions used to prepare imported data. The original source is not edited. Instead, the editor adds a step to the query, and the result appears when you load or refresh it.

For text columns, replacement can mean either changing a matching piece of text within a cell or replacing a whole cell when its contents match. For example, replacing St. with Street can turn 12 Oak St. into 12 Oak Street. Replacing the whole value Pending with In progress changes cells that contain that value, without changing a longer value such as Pending review when whole-cell matching is selected.

This distinction matters when values share words or abbreviations. Before applying a replacement to a large dataset, check a few representative rows and confirm that the operation changes only what you intend.

How to replace text in Excel for Windows

Open the workbook and locate the query. If it is already loaded, select Data > Queries & Connections, then double-click the query. To start with a new source, use Data > Get Data and load or transform the data.
In the Power Query Editor, select the text column you want to change. You can select more than one column by holding Ctrl while selecting column headers, but make sure each selected column contains compatible values.
Choose Transform > Replace Values. You can also right-click a selected column header and choose Replace Values.
In the dialog, enter the existing text in Value to Find and the new text in Replace With. Select Match entire cell contents if you want to replace only cells whose complete value matches the search text. Leave it unchecked to replace matching text within a cell.
Select OK, then inspect the preview and the new step in Applied Steps. Choose Home > Close & Load to return the result to Excel, or close and load to a chosen destination if you are setting up the query output.

The labels and available controls can vary slightly with the selected column’s data type and Excel build. These instructions describe Excel for Microsoft 365 on Windows. Excel for Microsoft 365 on Mac has Power Query features in supported builds, but the editor’s availability, connector support, and ribbon layout can differ. If the editor is available, look for the same Replace Values operation in its transformation controls; do not assume every Windows connector or authoring feature is present on Mac.

Partial matches versus entire-cell matches

A partial replacement is useful for standardizing fragments that appear in longer entries. If a column contains North-East, South-East, and East, replacing the text East with Eastern may affect every occurrence of that fragment, including the end of the first two values. That may be correct for a spelling cleanup, but not if only the standalone value should change.

Use Match entire cell contents for a category or status correction where only one complete value should be substituted. For example, changing NA to Not available as a whole-cell replacement avoids altering a value such as NA-West. If the option is unavailable or the result is not precise enough, filter the column, split it into appropriate fields, or use a conditional transformation rather than broad substring replacement.

Power Query also distinguishes an empty string from a null value. Replacing a phrase with an empty replacement removes that text but can leave a blank text value; it does not necessarily create a null. Null means no value is present, while an empty string is text with zero characters. This difference can affect filters, grouping, and later calculations.

Replace values in multiple columns or with M code

You can select multiple columns before using Replace Values when the same text substitution should apply to all of them. This is handy for cleaning repeated labels in several text fields. It is not a safe shortcut when the columns have different meanings: replacing — with an empty string may be appropriate in a description field but not in an identifier where the dash is significant.

Power Query stores transformations in the M language. A text replacement step commonly has this form, assuming the previous step is named Changed Type and the text column is named Company:

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

The step searches for the text Ltd. in the specified column and substitutes Limited. The final list identifies the affected column or columns; to target two columns, the list could contain both column names. The step name in the first argument must refer to the actual preceding step in that query, and column names must match exactly.

For whole-value replacement, the generated M step uses a value replacer rather than a text-fragment replacer. You usually do not need to write M by hand: the interface creates and maintains the step. Reading the formula bar or opening Home > Advanced Editor can nevertheless help you understand what the query will do. Be cautious when editing M directly because a misspelled step or column name can make the query fail.

Case, spaces, and punctuation

Text matching in Power Query is generally case-sensitive for text replacement. A search for east should not be assumed to match East or EAST. If data has inconsistent capitalization, decide whether to normalize it first—for example, with Transform > Format > lowercase or UPPERCASE—and then replace the standardized text. Normalizing changes the casing of the whole selected value, so it is not appropriate when capitalization carries meaning or needs to be preserved.

Spaces and punctuation are literal parts of text. A search for ABC includes a trailing space, while ABC does not. Leading or trailing spaces can make apparently identical values behave differently. Use the editor’s trim or clean transformations, found under the text-format controls, when the actual problem is stray whitespace or nonprinting characters rather than a phrase that needs substitution. Preview the result because trimming all values may also remove intentional spaces.

Replacement order can matter. Suppose a column contains both St. and St. John. Replacing the shorter fragment first may change the longer phrase before a later step can match it. Apply the more specific replacement first, or use a transformation designed to handle the whole value. Each replacement is a separate applied step, so the order is visible and can be rearranged or removed.

Data types, errors, and common problems

Power Query assigns data types such as text, whole number, date, and decimal number. A text replacement is intended for text values; a numeric or date column may require replacing a typed value instead of searching for a text fragment. If the dialog rejects an entry, produces errors, or does not affect the expected rows, check the column’s type in the header icon and review earlier steps such as Changed Type.

Nothing changes: Check spelling, capitalization, spaces, punctuation, and whether whole-cell matching is enabled. Also confirm that the selected column is the one containing the text.
Too much changes: Turn on whole-cell matching when appropriate, narrow the selected columns, or filter the rows before applying the replacement.
Some rows are errors: Inspect the type-conversion steps and the source values. A replacement cannot correct every malformed date or number, and changing text can expose a later conversion problem.
The change disappears after refresh: Power Query re-runs its steps against the current source. If the refreshed source no longer contains the target text, there will be nothing to replace; if it introduces a different spelling, the existing search will not match it.

For difficult cases, use View > Column quality or column profiling options if available in the editor to inspect errors, empty values, and value distributions. Filtering to a suspected value before replacing it is a simple way to verify the exact characters present. Remove an unwanted transformation from Applied Steps rather than trying to reverse it by making another broad replacement.

What happens when the query refreshes

A replacement step is repeatable: it is applied in sequence whenever Power Query refreshes the query. If the upstream data still contains Ltd., the refreshed result will again show Limited. This makes a query transformation more reliable than manually editing cells after each import, especially for recurring reports.

The output table is a result of the query, not the source data itself. Editing a loaded output cell is not a dependable way to maintain the correction; a refresh can overwrite that edit. Make the change in the query’s transformation steps, or correct the source if that is the appropriate place to maintain the data. Before distributing a workbook, refresh the query and confirm that the output reflects the intended source and replacement rules.

When to use another transformation

Replace Values is best when the same literal text needs a straightforward substitution. It is not the right tool for every cleanup task. Choose a transformation based on the structure of the data and the rule you need:

Use Trim or Clean for unwanted leading or trailing spaces and certain nonprinting characters, rather than replacing every possible whitespace pattern manually.
Use Split Column when a value contains separate pieces, such as a code and a description divided by a delimiter.
Use conditional logic when the replacement depends on another column or on several conditions, rather than one fixed search string.
Use Replace Errors when cells contain Power Query errors. Replacing visible text does not repair an error value.
Use a mapping table when many old labels must map to new labels. A merge with a maintained lookup table is often easier to audit and update than a long chain of individual replacement steps.

Worksheet Find and Replace is a different operation: it changes cells in the worksheet, while a Power Query step transforms data during import or refresh. Use worksheet replacement for a one-off change to ordinary cells; use Power Query when the correction belongs in a repeatable data-preparation process.

Frequently Asked Questions

Does Power Query replace every occurrence of text in a cell?
With a text-fragment replacement, Power Query replaces matching occurrences in the selected text values. Use the whole-cell matching option when you want to substitute only cells whose complete contents match.

Can Power Query replace text without changing the source file?
Yes. The replacement is a query transformation, and the loaded result is separate from the imported source. Refreshing reapplies the transformation; it does not write the replacement back to the source file.

Is Power Query text replacement case-sensitive?
Treat it as case-sensitive: a search for lowercase text should not be expected to match uppercase or title-case versions. Normalize case first if all variants should be handled alike, and check the preview.

How do I replace text with nothing?
Leave the replacement value empty to remove the matched text. The result may be an empty text value rather than null, so use a null-specific transformation if later steps need a genuinely missing value.

Can I replace values in more than one column at once?
Yes. Select the relevant columns before opening Replace Values, then verify the preview. The same search and replacement are applied to each selected column, so only group columns where that rule is valid.

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.