How to Replace Multiple Substrings in Power Query M

Power Query has functions to replace substrings. You can think of using Text.Replace in a custom column, or Table.ReplaceValue to directly replace values in a table. However, these functions are generally limited to performing just one replacement at a time.

But what if you need to replace multiple different substrings within the same dataset? This is a common scenario when cleaning your data. In this article, we’ll explore different techniques to replace multiple substrings in Power Query.

Method 1: Manual Replacements

The most straightforward way to replace multiple substrings in Power Query is by using the Replace Values operation multiple times. Each replacement creates a new step in the query editor, so the number of steps will match the number of replacements needed. Here’s an example:

let
    Source = MyData,
    #"Replaced Value1" = Table.ReplaceValue(Source," (GDR)","",Replacer.ReplaceText,{"country_txt"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," (FRG)","",Replacer.ReplaceText,{"country_txt"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","People's Republic of the Congo","Congo",Replacer.ReplaceText,{"country_txt"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Republic of the Congo","Congo",Replacer.ReplaceText,{"country_txt"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","-"," ",Replacer.ReplaceText,{"country_txt"})
in
    #"Replaced Value5"

In this example, each line performs a single replacement on the country_txt column. Each replacement step is clearly defined, but this approach has a downside: if you need a large number of replacements, it quickly becomes repetitive and cumbersome. Adding or modifying replacements also means adjusting multiple steps, which takes effort.

While this method is adequate for smaller datasets or when only a few replacements are needed, it lacks scalability. When extensive replacements are required, creating individual steps for each one takes effort and clutters the code.

If you’re looking for an more scalable way to handle multiple replacements in fewer steps, let’s explore an alternative method to simplify maintenance.

Method 2: Replacements with List.Accumulate

For a more maintainable approach to perform multiple replacements, you can use List.Accumulate. While it’s a complex function, it allows you to perform an operation repeatedly by iterating over a list.

To use this approach, start by defining a custom function, fxReplaceValues, that will handle your replacements:

( Table as table, listOfReplacements as list, listOfColumns as list ) =>
  List.Accumulate(
    listOfReplacements, 
    Table, 
    (state, current) =>
      Table.ReplaceValue(
        state, 
        current{0}, 
        current{1}, 
        Replacer.ReplaceText, 
        listOfColumns
      )
  )

The fxReplaceValues function takes three arguments:

• Table: the table where replacements will be performed.
• listOfReplacements: a list of lists, each containing an old value and a new value. For example: { {" (GDR)", ""}, {" (FRG)", ""} }.
• listOfColumns: the list of column names where replacements will occur. If you’re working with a single column, it should still be specified as a list, for instance: { "country_txt" }.

Once the function is defined, you can apply it to your table using a let expression. Here’s an example that uses fxReplaceValues on the country_txt column:

let
// --------------------Function Definition ------------------------------
    fxReplaceValues =
        ( Table as table, listOfReplacements as list, listOfColumns as list ) =>
        let
            myFunction =
                List.Accumulate (
                listOfReplacements, 
                Table, 
                ( state, current ) =>
                    Table.ReplaceValue ( 
                    state, 
                    current{0}, 
                    current{1}, 
                    Replacer.ReplaceText, listOfColumns )
                )
        in
            myFunction,
// -----------------------Query Start -----------------------------------
    Source = MyData,
    ReplaceValues = 
        fxReplaceValues( 
            Source, 
            { 
                {" (GDR)",""},
                { " (FRG)","" }, 
                { "People's Republic of the Congo","Congo" }, 
                { "Republic of the Congo","Congo" }, 
                { "-"," " } 
            }, 
            {"country_txt"}   
        )
in
    ReplaceValues

In this example:

• Source is your original data table.
• The step ReplaceValues calls fxReplaceValues, passing in:
  • The original table (Source).
  • A list of replacement pairs, such as {" (GDR)", ""} to replace ” (GDR)” with an empty string.
  • A list specifying the columns to apply these replacements to (in this case, {"country_txt"}).

The List.Accumulate method results in a clean, consolidated step that applies all replacements at once, which keeps your query steps minimal and easier to manage. When new replacements are needed, you can simply add another {old, new} pair to the listOfReplacements list. If you want to apply these replacements to multiple columns, just expand the list in the listOfColumns argument.

And in case you want to use this function throughout multiple queries, you can simply store the function in your Query Pane and apply it everywhere. That makes it so much easier to maintain the values to replace.

Next, we’ll explore yet another method that offers similar benefits, with a slightly different implementation approach.

Method 3: Replacements with List.Generate

The List.Generate function generally performs better than List.Accumulate and can achieve something similar. Yet it is bit more complex to set up.

To use this method, we’ll define a function similar to the previous example, but with List.Generate. For clarity, I’ve separated each of List.Generate’s argument with a spacing comment.

Here’s how the code is structured:

( Table as table, listOfReplacements as list, listOfColumns as list ) =>
  let
    myFunction = 
      List.Generate(
        () => 
        [
          myTable      = Table, 
          Index        = 0, 
          Replacements = listOfReplacements
        ], 
// ---------------------------------------------------------------------
      each [Index] <= List.Count([Replacements]), 
// ---------------------------------------------------------------------
      each [
        myTable = Table.ReplaceValue(
          [myTable], 
          Replacements{[Index]}{0}, 
          Replacements{[Index]}{1}, 
          Replacer.ReplaceText, 
          listOfColumns
        ), 
        Index = [Index] + 1, 
        Replacements = [Replacements]
      ], 
// ---------------------------------------------------------------------
      each [myTable]
    ), 
    Result = List.Last(myFunction)
  in
    Result

The function’s arguments are identical to the List.Accumulate function created earlier. Yet the function body is slightly more complex. Unlike List.Accumulate, List.Generate requires a manual increment of an index to keep track of the current replacement.

This function does the following:

1. Starts with an initial state: [ myTable = Table, Index = 0, Replacements = listOfReplacements ].
2. Iterates as long as [Index] is less than the count of [Replacements].
3. Applies each replacement using Table.ReplaceValue for the current index value, then updates the state by incrementing the Index.
4. Returns the final table after all replacements are complete, using List.Last(myFunction).

This structure allows List.Generate to perform replacements until all specified replacements are applied across the desired columns.

When you apply this to a query, this is what it looks like:

let
// --------------------Function Definition ------------------------------
  fxReplaceValues =
    ( Table as table, listOfReplacements as list, listOfColumns as list ) =>
    let
      myFunction =
        List.Generate( 
          () => 
            [ 
              myTable = Table, 
              Index = 0, 
              Replacements = listOfReplacements 
            ],
          each [Index] <= List.Count( [Replacements] ), 
          each 
            [ 
              myTable = 
                Table.ReplaceValue( 
                  [myTable], 
                  Replacements{ [Index] }{0},
                  Replacements{ [Index] }{1},
                  Replacer.ReplaceText, 
                  listOfColumns
                ), 
              Index = [Index] + 1,
              Replacements = [Replacements]
            ],
          each [myTable]
        ),
        Result = List.Last( myFunction )
    in
        Result,
// -----------------------Query Start---------------------------------
  Source = MyData,
  ReplaceValues = 
    fxReplaceValues( 
      Source, 
      {
        {" (GDR)",""},
        { " (FRG)","" }, 
        { "People's Republic of the Congo","Congo" }, 
        { "Republic of the Congo","Congo" }, 
        { "-"," " } 
      }, 
      {"country_txt"} 
    )
in
  ReplaceValues

Also in this setup, you can easily expand the list of replacements and the list of columns where the replacements should take place.

While List.Generate can seem a bit more complex than List.Accumulate, it provides a powerful, flexible method to perform multiple replacements with better performance, especially for large data transformations.

You can then convert the table to a list of lists by using the Table.ToList function.

Conclusion

Replacing values in Power Query is easy. The user interface provides a convenient button that generates a separate step for each replacement. If maintainability is important, you may consider using alternatives that bundle the replacements in a convenient list, like List.Accumulate and List.Generate