Power Query Problems

How to Replace Multiple Text Values in One Power Query Step

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to perform multiple partial text replacements within a large dataset (e.g., 8,000 rows) using a single Power Query applied step.

Product: Microsoft Excel (Power Query)
Device & OS: not provided
Scenario: Cleaning and transforming text data in a column by applying multiple specific text replacement rules (like swapping characters or adding prefixes) simultaneously without cluttering the query steps.
Observed behavior: Standard Find and Replace in Power Query creates a new applied step for every single replacement rule, which is inefficient for multiple rules across large datasets.

Before you start

Before editing your query with advanced functions, duplicate your original table or prepare a sanitized sample worksheet with representative data to safely test your M code transformations.

Solution 1Recommended

Use List.Accumulate in the Advanced Editor

This advanced method uses M code to loop through a predefined list of replacement pairs, applying all partial text replacements within a single Power Query step.

By default, the Power Query user interface creates a new step for every replacement. To consolidate them, you can leverage the List.Accumulate function in the Advanced Editor. This allows you to iterate over a list of old and new text pairs and apply them to your 8,000-row column efficiently.

Open the Advanced Editor

In the Power Query Editor, go to the 'Home' tab and click on 'Advanced Editor' in the Query group.

Define the Replacement List

Define a list of lists containing your replacement pairs above your transformation steps. For example: Replacements = {{"@", "*40"}, {"Org", "Com"}}.

Apply List.Accumulate

Modify your target column's transformation step to use List.Accumulate. The syntax generally looks like: List.Accumulate(Replacements, [TargetColumn], (state, current) => Text.Replace(state, current{0}, current{1})).

Save and Verify

Click 'Done' to close the Advanced Editor. Review your column to ensure the partial text values have been successfully updated in that single step.

Custom M Code Requirement: This solution requires manually writing M code. Ensure your replacement list matches the exact case of your dataset, as Power Query is case-sensitive.

Solution 2

Add a Conditional Column for Prefix Logic

If you need to add specific prefixes based on text conditions (like adding 'CP-PREF-' to values starting with specific strings), a conditional column is the easiest GUI-based approach.

Free Microsoft Office alternative

Try WPS Office for Fast and Easy Data Processing

While Power Query is natively built into Microsoft Excel, WPS Office provides a highly capable, free, and lightweight spreadsheet alternative. For users who prefer avoiding complex M code, WPS Spreadsheet allows you to easily perform complex text replacements using nested formulas, batch Replace tools, and advanced text functions.

1. Download and Install WPS Office: Visit the official WPS website, download the free installer, and complete the quick installation process.
2. Open Your Dataset: Launch WPS Spreadsheet and easily open your existing Excel workbook containing the text data.
3. Apply Replacements via Formulas: Use native functions like nested SUBSTITUTE() or IF() to clean your 8,000 rows quickly without relying on external query editors.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Lightweight installation with incredibly fast performance on large datasets.Intuitive user interface for standard data cleaning and text replacements.Cost-effective and entirely free to use for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I replace multiple values using the standard Replace Values button?

The standard 'Replace Values' button in the Power Query ribbon creates a new applied step for each replacement rule. To perform multiple replacements in a single step, you must edit the M code using functions like List.Accumulate.

Does List.Accumulate affect Power Query performance?

For extremely large datasets, heavily nested logic in List.Accumulate can occasionally impact query folding and slow down refresh times. However, for a dataset of 8,000 rows, the performance impact is usually negligible.

How do I handle partial string matches in multiple replacements?

You can use the Text.Replace function within your custom M code loop to swap specific substrings (like changing 'Org' to 'Com') without altering the rest of the text stored in the cell.

Why do support forums ask for a sanitized sample workbook?

Complex Power Query transformations depend heavily on the exact data structure and edge cases. Providing a representative sample without sensitive information allows experts to write and test the precise M code needed for your specific scenario.

Excel & Data Problems