Power Query Replace All Values In Column: Bulk Text Transformation Made Simple

I spent years manually editing cells until I discovered this method. One click transforms entire columns in Power Query without touching a single cell.

The magic happens in the Replace Values dialog—hidden in plain sight. You target the column, specify old and new values, and watch the entire dataset update instantly. No formulas, no macros, just clean data.

What took me 20 minutes in Excel now takes seconds. The best part? It remembers your replacements for future use, so repetitive tasks become effortless.

Works the same in Excel and Power BI. Between you and me, this is the feature I wish I’d learned on day one.

What Youll Need

Instructions

Here’s the process I follow to transform column data quickly without manual edits.

Open Excel and navigate to the Data tab. Select Get Data > From Table/Range if your data is already in a table, or import from your source. This opens the Power Query Editor where transformations happen.

Verify the column appears in the preview pane. If it doesn’t, check your source connection or table range. The data must load correctly before any transformations will work.

Click the column header containing the values you want to replace. The entire column will highlight, indicating it’s selected. I always double-check to ensure I’ve selected the right column—accidentally transforming the wrong one can undo hours of work.

If you need to replace values in multiple columns, you’ll repeat this process for each one. Power Query doesn’t support bulk column selection for this operation, so take it one at a time.

With the column selected, go to the Home tab in the Power Query Editor. Click Replace Values in the ribbon. This opens a dialog box where you’ll specify the value to find and the value to replace it with.

For case-sensitive replacements, check the Match case option. This is critical when dealing with proper nouns or mixed-case data where exact matching matters.

In the dialog box, enter the Value to Find and the Replace With value. You can replace with text, numbers, or even leave it blank to delete the value entirely. Click OK to apply the change.

The preview pane updates immediately, showing the transformation in real time. If the result isn’t what you expected, undo the step and adjust your values. Power Query keeps a history of all transformations, so you can always backtrack.

Once satisfied, click Close & Load in the Power Query Editor. This applies all transformations and returns the data to Excel. The replaced values will now appear in your worksheet.

If you need to edit the transformation later, go to Queries & Connections in Excel, right-click the query, and select Edit. This reopens Power Query with all your steps intact.

Tips and Common Mistakes

Here are the practical insights that will save you time and prevent common errors.

The Selection Check: Confession: I skipped verifying the column in Step 1 for years and wondered why transformations failed. Always confirm the column appears in the Power Query preview pane before proceeding—this single check prevents 90% of data loading issues.

Bulk Operation Limitation: In Step 2, remember Power Query doesn’t support selecting multiple columns at once for value replacement. You must process each column individually, which actually works to your advantage by forcing you to verify each transformation separately.

Case Sensitivity Trap: The Match case option in Step 3 is deceptively important. I learned this the hard way after replacing “Smith” but not “SMITH” in client data. For any dataset with proper nouns or inconsistent capitalization, this checkbox is your safety net.

Undo Wisdom: Step 4’s immediate preview updates are both a blessing and a curse. The blessing: you can see changes instantly. The curse: it’s tempting to rush. Always pause to verify the preview matches your expectations before clicking OK—Power Query’s undo history is powerful but not infinite.

Frequently Asked Questions

Replacing all values in a Power Query column is a game-changer for efficient data cleaning and transformation. 🎯 With just a few clicks, you can standardize text, fix errors, or reformat entire datasets. Ready to streamline your workflow? Dive into Power Query’s advanced features and explore more time-saving tricks for your next project!