Data cleaning often requires finding and replacing values across thousands of rows. Power Query makes this task efficient and repeatable through the Replace Values feature and advanced M language techniques. Whether you need simple find-and-replace operations, conditional replacements, or bulk updates from lookup tables, Power Query delivers powerful tools to transform your data accurately and consistently.

This comprehensive guide covers all methods to replace values in Power Query, from basic find-and-replace to advanced conditional replacements using M code.

Why Replace Values in Power Query?

The Replace Values feature is essential for professional data management:

• Data cleaning: Fix typos, standardize formats, and correct inconsistent entries
• Data normalization: Convert abbreviations to full names or vice versa
• Consistency: Apply replacements consistently across all rows
• Efficiency: Replace thousands of values in seconds
• Auditability: Every replacement is visible as a step in your query
• Reusability: Apply the same replacements automatically when data refreshes

1. Basic Find and Replace

Method: Using the Replace Values Dialog

The simplest way to replace values:

1. In Excel, go to Data > Edit Query (or Data > Get Data > Edit Query)
2. In the Power Query Editor, select the column containing values to replace
3. Go to the Home tab in the ribbon
4. Click Replace Values from the dropdown menu
5. In the dialog box:
   • Enter the value to find in Value To Find field
   • Enter the replacement value in Replace With field
   • Click OK
6. The replacement step appears in your Applied Steps list

Example:

Find “USA” and replace with “United States”

• Value To Find: USA
• Replace With: United States
• Result: All “USA” entries become “United States”

Important Notes:

• By default, this replaces entire cell contents
• To replace partial text (substrings), manually edit the M code (explained later)
• Replace operations are case-sensitive by default
• Each replacement operation adds a separate step

2. Replace Multiple Values

Method A: Multiple Replace Values Steps

Create multiple replacements sequentially:

1. Select the column to update
2. Click Replace Values
3. Enter first find/replace pair and click OK
4. Repeat steps 2-3 for each additional replacement

Disadvantage: Creates many steps and slows performance. For large numbers of replacements, use Method B instead.

Method B: Replace Values Using a Lookup Table

This efficient method replaces multiple values in one step using a lookup table:

1. Create a lookup table:
   • In your workbook, create a table with two columns: “Old” and “New”
   • Enter all find/replace pairs in these columns
   • Example: Old=”M”, New=”Male”; Old=”F”, New=”Female”
2. Load the lookup table into Power Query:
   • Select the lookup table in Excel
   • Go to Data > From Table/Range
   • Name this query “Replacements”
3. In your main query, add this M code:

   = List.Accumulate(
       {0..List.Count(Replacements[Old]) - 1},
       #"Previous Step",
       (state, current) => Table.ReplaceValue(
           state,
           Replacements[Old]{current},
           Replacements[New]{current},
           Replacer.ReplaceValue,
           {"ColumnName"}
       )
   )

4. Replace “ColumnName” with your actual column name
5. This replaces all pairs from your lookup table in a single step

3. Replace Text Patterns and Substrings

Method: Using Replacer.ReplaceText

Replace partial text within cells:

1. Select the column to modify
2. Click Replace Values
3. Enter the substring to find and replace
4. The default Replacer.ReplaceValue only works for entire cell contents
5. To replace substrings, manually edit the M code to use Replacer.ReplaceText

Manual M Code for Substring Replacement:

=Table.ReplaceValue(#"Previous Step", "old-substring", "new-substring", Replacer.ReplaceText, {"ColumnName"})

Example: Remove Domain from Email Addresses

=Table.ReplaceValue(#"Previous Step", "@domain.com", "", Replacer.ReplaceText, {"Email"})

Before: john@domain.com

After: john

4. Conditional Replacement

Method A: Using Conditional Column Feature

For simple conditional replacements:

1. In Power Query Editor, click Add Column > Conditional Column
2. Enter a new column name
3. Set up your conditions using the dialog
4. Example: If [Status]=”Active” then “Current” else “Inactive”
5. Click OK to create the column
6. Remove the original column and rename the new column if needed

Method B: Single-Step Conditional Replacement with M Code

Replace values in existing column based on conditions, without creating a new column:

=Table.ReplaceValue(
    #"Previous Step",
    each [ColumnToReplace],
    each if [ConditionColumn] = "X" then "ReplacementA" 
         else if [ConditionColumn] = "Y" then "ReplacementB"
         else [ColumnToReplace],
    Replacer.ReplaceValue,
    {"ColumnToReplace"}
)

Example: Department Name Update Based on Date

=Table.ReplaceValue(
    #"Previous Step",
    each [Department],
    each if Date.From([EffectiveDate]) >= #date(2024, 10, 1) 
         then "Cyber Security"
         else [Department],
    Replacer.ReplaceValue,
    {"Department"}
)

5. Replace Empty or Null Values

Method: Using Table.ReplaceValue

=Table.ReplaceValue(
    #"Previous Step",
    null,
    "Unknown",
    Replacer.ReplaceValue,
    {"ColumnName"}
)

This replaces all null values (empty cells) with “Unknown”.

Example: Replace Both Null and Empty Text

=Table.ReplaceValue(
    Table.ReplaceValue(
        #"Previous Step",
        null,
        "",
        Replacer.ReplaceValue,
        {"ColumnName"}
    ),
    "",
    "N/A",
    Replacer.ReplaceValue,
    {"ColumnName"}
)

6. Replace Values Across Multiple Columns

Method: Apply Replacement to Multiple Columns

=Table.ReplaceValue(
    #"Previous Step",
    "Old Value",
    "New Value",
    Replacer.ReplaceValue,
    {"Column1", "Column2", "Column3"}
)

This replaces “Old Value” with “New Value” in all three specified columns simultaneously.

7. Case-Insensitive Replacement

Method: Using Text.Lower or Text.Upper

For case-insensitive comparisons:

=Table.ReplaceValue(
    #"Previous Step",
    each Text.Lower([ColumnName]),
    each if Text.Lower([ColumnName]) = "usa" then "United States" else [ColumnName],
    Replacer.ReplaceValue,
    {"ColumnName"}
)

8. Replace Using Regular Expressions (Regex)

Method: Text.Remove or Custom Pattern Matching

Remove all non-numeric characters:

=each Text.Remove([PhoneNumber], {"-", "(", ")", " "})

This creates a new column with phone numbers stripped of formatting characters.

9. Bulk Replace Function (Advanced)

Method: Create a Reusable Function

For complex scenarios with multiple replacements:

let
    ReplaceMultiple = (InputTable as table, ColumnName as text, ReplacementList as list) as table =>
    let
        Replace = (Table as table, Replacements as list) as table =>
            List.Accumulate(
                Replacements,
                Table,
                (state, current) => Table.ReplaceValue(
                    state,
                    current{0},
                    current{1},
                    Replacer.ReplaceValue,
                    {ColumnName}
                )
            )
    in
        Replace(InputTable, ReplacementList)
in
    ReplaceMultiple

Usage in your query:

=ReplaceMultiple(#"Previous Step", "Status", {{"A", "Active"}, {"I", "Inactive"}, {"P", "Pending"}})

10. Replace Methods Comparison

Method	Best For	Complexity	Performance
Basic Replace	Single replacements	Very Easy	Fast
Multiple Replaces	<5 replacements	Easy	Slower (many steps)
Lookup Table	5+ replacements	Moderate	Fast (single step)
Conditional	Condition-based changes	Moderate	Fast
Regex/Pattern	Complex patterns	Complex	Moderate

11. Common Replacement Scenarios

Scenario 1: Standardize Country Names

Replace country abbreviations with full names:

• “US” → “United States”
• “UK” → “United Kingdom”
• “CA” → “Canada”
• “AU” → “Australia”

Use Method: Lookup Table (Section 2, Method B)

Scenario 2: Fix Typos in Product Names

Replace common misspellings:

• “Seperator” → “Separator”
• “Occured” → “Occurred”
• “Recieve” → “Receive”

Scenario 3: Clean Phone Numbers

Remove formatting characters from phone numbers:

=each Text.Remove([Phone], {"-", "(", ")", " "})

Scenario 4: Update Department Names Based on Date

=Table.ReplaceValue(
    #"Previous Step",
    each [Department],
    each if [Department] = "Cloud Security" and [TransactionDate] >= #date(2024, 10, 1)
         then "Cyber Security"
         else [Department],
    Replacer.ReplaceValue,
    {"Department"}
)

12. Best Practices for Replacing Values

Data Quality

• Verify replacements: Always check results against source data before deployment
• Create audit trail: Document what replacements were made and why
• Test with sample: Run replacements on small dataset first
• Backup original: Keep original column for comparison if needed

Performance

• Minimize steps: Use lookup tables instead of multiple Replace steps
• Order matters: Place Replace steps after filtering to reduce affected rows
• Use efficient functions: Native M functions are faster than custom logic
• Cache results: If using same lookup table multiple times, load it once

Maintainability

• Document replacements: Explain why replacements were needed
• Keep lookup tables: Maintain external reference tables for easy updates
• Use meaningful names: Name queries and steps descriptively
• Version control: Track changes to replacement logic

13. Troubleshooting Replacement Issues

Problem: “Replacement didn’t work”

Solutions:

• Check for extra spaces or hidden characters using Text.Trim()
• Verify case sensitivity matches your replacement value
• Ensure you’re looking in the correct column
• Check if value exists in the data before creating replacement step

Problem: “Only some values were replaced”

Solutions:

• Check if different variations exist (different cases, spaces, etc.)
• Use substring replacement with Replacer.ReplaceText instead of ReplaceValue
• Apply multiple replacement steps for different variations

Problem: “Performance is very slow”

Solutions:

• Combine multiple Replace steps into single lookup table operation
• Move Replace steps later in your query (after filtering)
• Check if lookup table is causing performance issues
• Consider using simpler replacements with fewer conditions

Master these techniques to automate repetitive data cleaning tasks and ensure consistent data quality across your entire dataset.