How to How to Use Replace Values in Power Query in Excel in Excel - Practical Guide

Learn how to use Replace Values in Power Query to efficiently find and replace text, numbers, or patterns across entire datasets. This feature is essential for data cleaning, standardizing inconsistent entries, and preparing raw data for analysis without writing formulas.

Why This Matters

Replace Values saves time on manual data cleaning and ensures consistency across large datasets, improving data quality and analysis accuracy. It's a core skill for data professionals managing real-world, messy data sources.

Prerequisites

•Access to Excel with Power Query feature (Excel 2016 or later)
•Understanding of basic Power Query interface and loading data sources

Step-by-Step Instructions

Load or access your data in Power Query

Open Excel > Data > Get Data > From File/Database, or double-click an existing query to enter the Power Query Editor window.

Select the column containing values to replace

Click the column header in the Power Query Editor to highlight the entire column you want to edit.

Open the Replace Values dialog

Go to Home tab > Replace Values button (in the ribbon under Transform group) to open the Replace dialog box.

Enter search and replacement values

Type the exact value to find in 'Value To Find' field and the replacement text in 'Replace With' field, then click OK.

Load the cleaned data back to Excel

Click Home > Close & Load to apply changes and import the updated dataset into your Excel worksheet.

Alternative Methods

Using Find & Replace with regex patterns

Enable regular expressions in Replace Values dialog for complex pattern matching, allowing you to replace values based on formulas or wildcards.

Replace using conditional columns

Create a new column with conditional logic (if-then statements) to replace values based on multiple conditions before removing the original column.

Replace via the Formula.ReplaceText function

Use Power Query's native M formula language with Text.Replace() function for advanced replacements in custom columns.

Tips & Tricks

✓Use Replace Values on entire columns rather than cell ranges for faster processing of large datasets.
✓Always preview the results before closing the Replace Values dialog by checking affected rows in the query editor.
✓Replace Values is case-sensitive by default; use custom formulas if you need case-insensitive replacements.

Pro Tips

★Chain multiple Replace Values steps by repeating the action; Power Query records each replacement as a separate transformation step in the query editor.
★Use Replace Values with conditional grouping to standardize category names, product IDs, or regional codes across different data sources.
★Export replacement mappings as a reference table to document data cleaning processes for audit trails and reproducibility.

Troubleshooting

Replace Values button is greyed out in Power Query Editor

Ensure you have selected a column by clicking its header. The button only activates when a valid column is selected, not individual cells.

Replacement didn't affect all expected rows

Check for hidden characters, extra spaces, or case sensitivity differences. Use Trim function first to remove whitespace, then retry Replace Values.

Dialog shows 'No matches found' despite visible matching values

Verify exact spelling and spacing of your search term. Power Query matches complete cell values; partial matches require regex or formula-based approaches.

Related Excel Formulas

SUBSTITUTE FIND Text.Replace (M language)Text.Trim

Frequently Asked Questions

Can I use wildcards or regex patterns in Replace Values?

Standard Replace Values uses exact matches only, but you can enable regular expressions in advanced options for pattern-based replacements like '.*old.*' to find any cell containing 'old'.

Will Replace Values affect my original Excel file?

No, Replace Values operates only within Power Query before loading. Your original data source remains unchanged, and you control whether to overwrite or create new sheets in Excel.

Can I undo a Replace Values action after loading data into Excel?

Yes, edit the Power Query to remove or modify the replacement step, then refresh. Alternatively, use Excel's Undo (Ctrl+Z) immediately after loading if you haven't saved.