Multiple replacements or translations in Power BI and Power Query

A common task when cleaning data is to perform multiple replacements at once. When dealing with “translation tables” you might have come across Chris Webb’s solution using List.Generate to deal with this. This works fine – but might deliver unexpected results if you don’t consider it’s implications properly. Say we add lines like shown below, then we might expect the result as highlighted in yellow:

Image2

Multiple replacements using 2 different techniques

So hey – what happened here?: Our catamaran turns out as a bearamaran and the chair turns to chwater.

How it works:

Because there could be more than one word per line that needs to be replaced, the function Text.Replace alone wouldn’t be able to do this job as it takes only one replacement element. This is where List.Generate jumps in: Taking the result of the previous replacement step as input and performs the next replacement – as often as there are elements in the replacement list.

And Text.Replace is a function that searches for substrings in the text. That way “catamaran” turns out as a “bearamaran”.

But what happened to the mat where the cat and the dog sat on: “chwater”?

Firstly “mat” has been replaced by “chair”. But due to the recursive nature of the List.Generate-function, this result is not safe. So when Text.Replace kicks in on: Replace “air” with “water”, searching again also for substrings, a part of “chair” with be replaced by water: “chwater“.

So while Chris’ article is an excellent explanation of how List.Generate works, most multiple text replacement requirements in the real world might be better solved with a different approach:

Use List.ReplaceMatchingItems instead

This is a function that is designed to work on lists and performing replacements for multiple items (that also need to be fed in as list):

But we want to replace within a text, not within a list!

No problem for Power Query as the master of transformations: We just temporarily transform our text into a list, perform the replacements and then transform our list back into text – as if nothing has happened 🙂

Text.Split(text, ” “) will split out text strings into a list of single words because we choose blank as the delimiter

Text.Combine(list, ” “) will then reassemble.

This technique is useful in many other operations as well. Sometimes the delimiters will change, but as long as you don’t get too mad with your transformations in between, it is often a easy and safe way to take a useful function from the List-category to perform on Text-fields.

But there’s another hurdle to take here: How to get our translation table into the desired format for this function? Have a look at the documentation:

List.ReplaceMatchingItems ( {1, 2, 3, 4, 5}, { {2, -2}, {3, -3} } ) equals { 1, -2, -3, 4, 5}

So we’re fine with lists: Identifying them by their curly brackets like in the result: { 1, -2, -3, 4, 5}

But what the x is this?: { {2, -2}, {3, -3} } a list containing lists as it’s elements.

List of Lists

Have you ever worked with a table with one column only? Maybe also have been confronted with a surprising effect that this had automatically turned itself into list-format? So if our table just had one column, it would almost be a list already. So let’s try to get there: Merge the columns of the translation table into one so that the contents of the rows (each cell effectively) will be returned as a list (as we want to maintain the separation that the columns gave, but not their format).

Table.AddColumn(Source, “Liste”, each ( { [Word To Replace], [Replace With] } ) )

This is an added column where in each row of the table, the specific rows contents of [Word To Replace] and [Replace With] will be passed into the list. So for every single row one list will be created.

If you’re interested in the techniques behind this or have guessed that instead one list with 2 full column would be returned: [Word To Replace] and [Replace With]: The unimposing little word “each” actually creates a sort of a row context. Actually the expression at the 3rd place in the “Table.AddColumn”-expression is expected to be a function and that’s what “each” stands for. It is basically syntax sugar for a full function expression like described here.

So: Goodbye columns “WordsToReplace” and “ReplaceWith”, but we respect your destiny and maintain your separating character by keeping your contents separated in the list-format instead.

Nice side-effect: It’s also way much faster.

And here is the full code:

let
    Source = ReplacementsTable,
    CreateListOfLists = Table.AddColumn(Source, "Liste", each ({[Word To Replace], [Replace With]})),
    Text = Text,
    TurnTextToList = Table.AddColumn(Text, "Custom", each Text.Split([Text], " ")),
    Replacements = Table.AddColumn(TurnTextToList, "Changed Text Expected", 
                each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste])," ")),
    Cleanup = Table.RemoveColumns(Replacements,{"Custom", "Text"})
in
    Cleanup

& the file: M_MultipleReplacementsV3.xlsx

.. and a “functionized version”:


let func =
(
  TableWithTextColumn as table, 
  ColumnNameTextToReplace as text, 
  ReplacementsTable as table, 
  ColumnNameWordToReplace as text, 
  ColumnNameReplaceWith as text
) => 
  let
    Source = ReplacementsTable,
    CreateListOfLists = Table.AddColumn(
        Source, 
        "Liste", 
        each ({Record.Field(_, ColumnNameWordToReplace), Record.Field(_, ColumnNameReplaceWith)})
      ),
    Text = TableWithTextColumn,
    TurnTextToList = Table.AddColumn(
        Text, 
        "Custom", 
        each Text.Split(Record.Field(_, ColumnNameTextToReplace), " ")
      ),
    Replacements = Table.AddColumn(
        TurnTextToList, 
        "Changed Text Expected", 
        each Text.Combine(List.ReplaceMatchingItems([Custom], CreateListOfLists[Liste]), " ")
      ),
    Cleanup = Table.RemoveColumns(Replacements, {"Custom"})
  in
    Cleanup ,
documentation = [
Documentation.Name = " Table.ReplaceMultiple.pq ",
Documentation.Description = " Replaces multiple values at a time in a table column without recursion. ",
Documentation.LongDescription = " Replaces multiple values at a time in a table column without recursion: http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/ The <code>ReplacementsTable </code> must have the values to be replaced in the 1st column and the new value in the 2nd. ",
Documentation.Category = " Table.ColumnOperations ",
Documentation.Source = " http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/ . ",
Documentation.Version = " 2.0 ",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description = " ",
Code = " let
TableWithText = #table( {""Text""}, List.Zip( { {""the cat sat on the mat"" ,""the cat sat next to the dog"" ,""the dog chased the cat"" ,""the dog sat on the mat"" ,""the catamaran sails through the air""} } ) ),
ReplacementsTable = #table( {""Word To Replace"", ""Replace With""}, List.Zip( { {""cat"" ,""mat"" ,""dog"" ,""the"" ,""air""}, {""bear"" ,""chair"" ,""dragon"" ,""THE"" ,""water""} } ) ),
// The query containing the function has to be called ""fnTable_ReplaceMultiple_pq""
Result = fnTable_ReplaceMultiple_pq ( TableWithText , ""Text"", ReplacementsTable, ""Word To Replace"", ""Replace With"" )
in
Result ",
Result = " Changed Text Expected
———————
THE bear sat on THE chair
THE bear sat next to THE dragon
THE dragon chased THE bear
THE dragon sat on THE chair
THE catamaran sails through THE water "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

Enjoy & stay queryious 🙂

Comments (63) Write a comment

  1. Hi Imke 🙂
    Very elegant solution 🙂
    If you want to speed it up more, you may use List.Buffer function. I mean something like this
    CreateListOfLists = List.Buffer(Table.AddColumn(Source, “Liste”, each ({[Word To Replace], [Replace With]}))[Liste]),
    and then you have to change a little Replacements step to this below
    Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”,
    each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists),” “)),
    Of course this is a proposal only, because your solution should works fine without these changes 🙂

    From me, another approach (just different)

    let
    Repl = Table.Buffer(ReplacementsTable),
    Text = Text,
    Result = Table.AddColumn(Text, “Changed Text Expected”,
    each Text.Combine(
    List.Transform(
    Text.Split([Text], ” “),
    each
    try Repl[Replace With]{List.PositionOf(Repl[Word To Replace], _)}
    otherwise _
    ),
    ” ” )
    )
    [[Changed Text Expected]]
    in
    Result

    sq 🙂

    Reply

    • Hi Bill,
      thanks for the accolade. Actually, I found my solution quite elegant as well – until you came around …
      But yours is stellar, so I can comfort myself a bit in thinking that within earthly scale it still shines a little 🙂

      Note to my readers: This cosmic M-agician shows us how to make multiple replacements without a dedicated replacement-operation but with the most elegant lookup-operation I’ve come across in M instead: No join-expand-if-then but simple try lookup otherwise old value. Also he returns a table with one column with this statement: [[Changed Text Expected]] which I still need my head to turn around, as it looks as if it turns a list into a table, but actually only seems to work on table columns and not on native lists. @Bill: Any idea why that is?

      Apart from that: Thank you so much Bill and applause, this was a real experience!

      And yes: List.Buffer speeds it up considerably! But I might actually prefer this version then:

      let
      Source = ReplacementsTable,
      CreateListOfLists = Table.Buffer(Table.AddColumn(Source, “Liste”, each ({[Word To Replace], [Replace With]}))[[Liste]]),
      TurnTextToList = Table.AddColumn(Text, “Custom”, each Text.Split([Text], ” “)),
      Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”, each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),” “))[[Changed Text Expected]]
      in
      Replacements

      Fan of double-brackets already :-))

      Reply

      • Hello Imke,

        I have read this post and tried to utilize this knowledge. Though I was able to set up the things in my model, there seems to be some items from my list doesn’t get replaced when I close in around 1000 rows of data. My data is not huge just below 3000 in total. Would you like to have a look at my file to help me out identify the issue please ?

        Reply

      • Hi Imke,

        I think I followed the logic above but I get the Error, Unexpected error: An item with the same key has already been added. Is there anything obvious I’ve done wrong here. You’r help is greatly appreciated. The steps work up to Replacements but then I get the error.

        let
        Source = Words,
        CreateListOfLists = Table.Buffer(Table.AddColumn(Source, “Liste”, each ({[OldWords],[NewWords]}))[[Liste]]),

        TurnTextToList = Table.AddColumn(Counts, “Custom”, each Text.Split([Description], ” “)),

        Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”, each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),” “))[[Changed Text Expected]]

        in
        Replacements

        Reply

        • Hi HD,
          that’s probably because your “Counts”-table has a column named “Custom”.
          So you either rename that or change the column name in my code (in step “TurnTextToList” and in step “Replacement”).

          Reply

          • I tried replacing “Custom” with “unique” in both places but the error remains. The “Counts” table only had one column in it with a different heading.

          • I realized the error was because I had repeated a word twice in my Stopwords list.

            Thanks for your help, it’s a great piece of code.

  2. Thanks for your kind words, Imke. 🙂
    To understand this syntax (double brackets), look at “6.4.2 Field Access” in Formula Language Specification (page 61)

    Regards and sq 🙂

    Reply

    • Hi Bill,
      The Formula Language Specification (“6.4.2 Field Access”) is currently right in front my eyes.
      Even knowing what you showed here about [[ ]], I would have never understood it from the documentation !!!
      The only hint is the world “Table” briefly written down on the second line. I wonder how you figured it out.

      Reply

  3. Thanks Bill – looks like I need to learn some basics finally… (a list is missing the identifier and therefore cannot be selected or projected?)
    sq 🙂

    Reply

  4. Simply Genius, Thank you for your post.

    How do I go about getting the “Replace With” as my final result, i.e instead of particular ‘word’ replace, I would like the replace word to be the column result.

    Text Word To Replace Replace With Expected Result
    the cat sat on the mat cat bear bear

    Thank you

    Reply

  5. Text = the cat sat on the mat,
    Word To Replace = cat,
    Replace With = bear,
    Expected Result = bear

    Reply

    • Just to make sure to understand your requirement correctly: You want to replace multiple words of whom one is “cat” with just one word “bear”?

      Reply

  6. That is Correct. Instead of the whole sentence- I would like the result to display just one word “bear”.

    Thank you for looking into it.

    Reply

    • This can be achieved by modifying the last step “Replacements” by this: Table.AddColumn(TurnTextToList, "Changed Text Expected", each List.First(List.Select(CreateListOfLists[Liste], (Match) => List.Contains([Custom],Match{0}))){1})

      It will return the first match that is found in the list. If you want the last match instead, you simply replace “List.First” by “List.Last”.

      Hope this helps and cheers, Imke

      Reply

  7. Hi! This is was super helpful – thank you so much for posting this. Is there is a way to replace the text directly in the original column instead of adding the new column “Changed Text Expected”? I’ve been trying to modify the code to do this with no such luck. Any insight would be greatly appreciated! Thank you.

    Reply

    • Hi Ayla,
      yes, this can be done like this: = Table.TransformColumns(Text,{{“Text”, each Text.Combine(List.ReplaceMatchingItems(Text.Split(_, ” “), CreateListOfLists[Liste]),” “)}})
      Works with the data in the downloadable file.
      Cheers, Imke

      Reply

        • Hi Ayla,

          Where do I, exactly, have to change the code?
          I am new in power query, but this function helped me a lot.

          I have changed the chunk of code of your reply by =>

          Replacements = Table.AddColumn(
          TurnTextToList,
          "Changed Text Expected",
          each Text.Combine(List.ReplaceMatchingItems([Custom], CreateListOfLists[Liste]), " ")
          )

          Really thanks for your help!

          Reply

        • Hi Ayla,

          I am a newbie in power query, and i would like to replicate this change in the code. Where exactly do i have to replace it in the code?

          I reallu appreciate your help 🙂

          Reply

  8. Hi there,

    First, thanks a lot for this post and many others that have been super useful.
    What I’m gonna ask may be really simple to extrapolate from your query but I haven’t been successful so far. Could I replace the values in my already-existing column, without using the split function so I replace full strings by another full string.

    For example, I want to replace all cases where the value “Two words” is found by the value “Other three words”. There are obviously more values to replace on the same column.

    Many thanks for your time,

    Reply

    • Currently, I see only Chris’ method for this use case.
      Do you see the risk that your “Two words” phrases will be included in other phrases as will and you’ll actually run into the problems I’ve described?

      Reply

      • Hi!
        Thanks for quick reply. I did understand the problems of Chris´method, that’s why I opted to follow this solution. I made a few changes to your query to make it more robust and it works fine.
        There is a couple of cases I have to check first and, if it works for all of them, I’ll let you know!

        Regards,

        NAOS

        Reply

  9. Pingback: Transform text with exceptions in Power BI and Power Query – The BIccountant

  10. Hi,
    Thanks a lot for all the examples and explanations.
    I’m trying to exchange whole text string values in a column. The problem I have is that it returns the whole list for every record. What do I wrong? I just want to have the exchanged value in the column.
    #”CAL freq list ” = Table.AddColumn(#”Replaced Value1″, “Updated freq”, each List.ReplaceMatchingItems(#”Replaced Value1″[#”CAL freq.”], Frequencies))

    Frequencies is a list with search value and replace value. {search value 1, replace value 1},{{search value 2, replace value 2}, …..

    Kind regards,
    Christiaan

    Reply

    • Hi Christiaan,
      try omitting the table name:

      #”CAL freq list ” = Table.AddColumn(#”Replaced Value1″, “Updated freq”, each List.ReplaceMatchingItems(#”Replaced Value1″[#”CAL freq.”], Frequencies))

      If this doesn’t work, please paste the full code.
      /Imke

      Reply

  11. Hi everyone, this is great! Thanks for the post.

    I’m having trouble getting a string with multiple words to be replaced properly.

    For example, my transformation table has:
    Business Development –> BD
    Marketing –> MKTG
    Medical Affairs –> CDMA

    and my [Column] (using Imke’s example) has strings such as:
    Business Development;Marketing;Medical Affairs
    Marketing
    Medical Affairs;Marketing.

    The result I’m getting is:
    Business Development;MKTG;Medical Affairs
    MKTG
    Medical Affairs;MKTG

    which leads me to believe the space inbetween the two words to be replaced is an issue. I have tried removing that/trimming text, etc., to no avail.

    Any suggestions?

    Reply

    • Hi tcp,
      which delimiter are you using in Text.Split(text, ” “) ?
      I’ve used the space, but for your case, you’d have to juse “;”.

      /Imke

      Reply

      • Yes, of course! I was mistakenly replacing the space in Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),” “)).

        Thanks 🙂

        Reply

  12. How do i use this query in a merged query where already many steps are carried out and initial source file is different?

    Reply

  13. Hi, Thanks for the great post – this looks exactly like what I need. However, I am getting the following error on the line “Text = Text” when trying tio use the function in Power BI Desktop:

    “Expression.Error: The import Text matches no exports. Did you miss a module reference?”

    I have only changed the ReplacementTable and field references in teh ReplacementTable. Please could someone explain what the purpose of the “Text = Text” line is and what I need to do to get it working??

    Here is my code:

    let
    Source = #”Fuzzy Transformations”,
    CreateListOfLists = Table.AddColumn(Source, “List”, each ({[From], [To]})),
    Text = Text,
    TurnTextToList = Table.AddColumn(Text, “Custom”, each Text.Split([Text], ” “)),
    Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”,
    each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[List]),” “)),
    Cleanup = Table.RemoveColumns(Replacements,{“Custom”, “Text”})
    in
    Cleanup

    Reply

    • Hi Ron,
      Text has to reference the table that contains the words to replace. The column in there also has to be named “Text”.
      Otherwise you’d have to adjust this code accordingly: TurnTextToList = Table.AddColumn(Text, “Custom”, each Text.Split([Text], ” “)),

      The Text in square brackets stands for the column name and that Text without brackets stands for the table itselft.
      /Imke

      Reply

    • Regarding the Text = Text statement, according to what I understand, the left “Text” is the name of the table and the right “Text” is the name of the query that contains the table (and also the name of the column :-)).
      You should then change the text 😉 accordingly.
      but, it (the Text=Text) is not strictly necessary if, in the following statement, you use the name of the query containing the table instead of “Text”
      Table.AddColumn (NameQueryContaningTable, “Changed Text Expected”, …

      Reply

  14. Hi, I’m a beginner with Power Query, but I like it a lot even if it’s not easy to find out useful documentation. I was lucky enough to come across this interesting blog and follow the discussion and learn the remarkable potential of the language.
    I would be pleased to submit the following variant (which uses a sort of dictionary) to the brilliant solutions you have above illustrated.

    let

    Dict = Record.FromList( Source[Replace With],Source[Word To Replace]),
    TurnTextToList = Table.AddColumn(testo, “Custom”, each Text.Split([Text], ” “)),
    Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”, each Text.Combine(List.Transform([Custom],each Record.FieldOrDefault(Dict,,)),” “))//[[Changed Text Expected]]
    in
    Replacements

    Reply

  15. I am using this function to try to clean a list of roughly 20,000 scandinavian company names. My replace column is mostly blanks because I am removing ” OY” and “AB ” etc. A number of the rows have numbers. Is this why I could be getting a PowerBI expression error of “We can not convert a value of type List to type Table. Details Value = [List] Type = [Type].

    Or is this happening perhaps because in lieu of [Text] I am referencing a table[columnname] such as CustomerNames[Names]?

    Imke is the best powerBI power genius! Thanks, IMKE

    Reply

    • Thanks Charles 🙂
      This probably means that the step “Text” in the row before returns a list instead of a table.
      It must be the table with the text to replace in the column “Text” in it.
      /Imke

      Reply

      • @Imke – that edit fixed the issue. Thank you.

        Question: how would you edit this script to make it a string / character replacement for the following situation, where currently I’m not getting replacements with this script.

        Here’s the usecase: I have a table of company names that I want to normalize. Let’s say the company names are mostly German (although in reality they are mostly Scandinavian, which is a tougher normalization assignment!):

        These are the names to normalize:

        Names[Text] // ie Table called Names, Column to normalize called Text
        Volkswagen, Inc.
        Volkswagen GMBH
        Daimler AG
        Daimler KG
        Robert Bosch GmbH
        KG Daimler
        SAP SE
        SAP AG
        Mercedes
        AGFA-Gevaert Group AG

        The ReplacementsTable would focus mostly on characters to replace:
        //Note the spaces needed for clean replacement. AG below is ‘ AG. KG is KG ‘
        //Also note that many of the Replace With fields are blank because we want those characters replaced by nothing — just deleted.

        To Replace Replace With
        , Inc.
        GMBH
        AG
        KG
        GMBH
        GmbH
        KG
        SE
        AG
        Mercedes Daimler

        The end result would be removal of the company signifiers to normalize the names.
        Volkswagen
        Volkswagen
        Daimler
        Daimler
        Robert Bosch
        Daimler
        SAP
        SAP
        Daimler
        AGFA-Gevaert Group //AG not replaced because string above was ‘ AG (space before AG)

        I currently do this with a massively long find value and replace M query script. How would Imke master this? Is it worth trying to extend your formulae to this use case?

        In worshipful reverence!
        Charlie

        Reply

        • Hi Charles,
          for my understanding, it should work with the replacement of the empty values like you’ve described.
          What am I missing?
          /Imke

          Reply

  16. Hi Imke,
    This is great. What if i need to replace a phrase, e.g. the cat -> A BEAR. Chris’s solution can handle that, but has the recursive issue – chwater 🙁
    Thanks,

    Reply

    • Hi Di,
      the solution for this depends on the characters that surround the phrase you’re searching: Is it always a blank/space or could it be signs like parenthesis, comma, colon and so on?
      Are you able to specify or give a list of possible characters?
      /Imke

      Reply

  17. Hi Imke,

    Totally understand your logic now depends on delimiter e.g. space to split text to list, then compareing 2 list. What I’m looking for is not split the text (similar logic like Chris by using Text.replace because no rule to split) but fix the issue of chwater.
    So just use exact sample data from Chris, but change the 1st record to “the cat” => “A BEAR”. I would expect result of all “the cat” replaced by “A BEAR’.

    Thanks, Di

    Reply

  18. BTW, i think my request is not making sense since there is no way to tell which word to stop replacement. Please ignore it.

    Reply

  19. Hi Imke, is there a simple way to adapt this approach to pad each delimited value with leading zeros in order to correctly (alpha) sort a column with e.g. 1.10.2.1. I would like to pad each delimited value with 3 zeros e.g. 001.010.002.001.
    Thanks, Brent

    Reply

  20. It seems the code is hardcoded to “Text” column name. I modified the last few lines of the m code to handle this:
    // Had to add/modify the next two lines to use ColumnNameTextToReplace instead of hardcoded “Text” column name
    #”Removed Columns” = Table.RemoveColumns(Replacements,{ColumnNameTextToReplace, “Custom”}),
    Cleanup = Table.RenameColumns(#”Removed Columns”,{{“Changed Text Expected”, ColumnNameTextToReplace}})
    in
    Cleanup ,

    Reply

  21. Pingback: New M function: Text.ReplaceAll – XXL BI

  22. Pingback: PQ text replacement competition – XXL BI

  23. Thank you for this solution. How could I make it to that it replaces the full string?

    let
    Source = ReplacementsTable,
    CreateListOfLists = Table.AddColumn(Source, “Liste”, each ({[Word from], [Word to]})),
    Text = OLM[Department],
    TurnTextToList = Table.AddColumn(OLM, “Custom”, each Text.Split([Department], ” “)),
    Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”, each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),” “)),
    Cleanup = Table.RemoveColumns(Replacements,{“Custom”, “Department”}),
    #”Changed Type” = Table.TransformColumnTypes(Cleanup,{{“Changed Text Expected”, type text}})
    in
    #”Changed Type”

    Thank you

    Reply

    • Hi Lebene,
      if you want to replace full strings, I strongly recommend to do a standard merge with the replacements table.
      That doesn’t require individual code and should also be the fastest option.
      /Imke

      Reply

      • Thank you Imke, I came to this conclusion as well as I searched further (Chandoo had the perfect solution I needed) but definitely bookmarked this for future need.

        Reply

  24. Hi Imke,

    thank you for this approach! Is it possible to replace only words from a related table?
    I have a table with a “Document Id” column and a “TextToReplace” column. And I created a Table with a related “Document Id” column and the columns “WordToReplace” and “ReplaceWith”. But this query should not replace words, if the document Ids are not matching. Is this even possible with Power Query?

    [TableWithTextColumn]

    Document Id | TextToReplace

    1941234719 | Hello World
    7543234719 | Another Hello World text

    [ReplacementTable]
    Document Id | WordToReplace | ReplaceWith
    1941234719 | World | Imke,
    1941234719 | Another | The BI Accountant
    7543234719 | Another | Thank
    7543234719 | Hello | you
    7543234719 | World | for
    7543234719 | text | your help

    [ExpectedResult]

    Document Id | ReplacedText

    1941234719 | Hello Imke,
    7543234719 | Thank you for your help

    Thank you and best regards
    Johannes

    Reply

  25. Hello, thank you for this it is what I have been looking for

    I am using this

    let
    Repl = Table.Buffer(ReplacementsTable),
    Text = Text,
    Result = Table.AddColumn(Text, “Changed Text Expected”,
    each Text.Combine(
    List.Transform(
    Text.Split([Text], “|”),
    each
    try Repl[Replace With]{List.PositionOf(Repl[Word To Replace], _)}
    otherwise _
    ),
    “|” )
    )
    [[Changed Text Expected]]
    in
    Result

    where I added a pipe delimiter

    Is it possible to add a trim at this point?

    Text.Split([Text], “|”),

    I have strings that look like

    the|cat sat |on the mat

    find replace
    cat sat >> bear

    does no get replaced because of the space after the sat in “the|cat sat |on the mat”

    Reply

  26. Hi. What if I can’t use a delimiter? Since I want to find “Won T” within a phrase and replace it with “Won’t”.

    Reply

  27. Hello. I’m a COMPLETE newbie to Power Query and M.

    I tried using the function and am getting an error:

    An error occurred in the ‘ReplaceMultipleValues’ query. Expression.Error: We cannot convert the value “CleanupZonas” to type Table.
    Details:
    Value=CleanupZonas
    Type=[Type]

    I have three tables.

    Source with names to normalize has several columns and including the following. The names to normalize are in a table named ‘Data’ and look like this:

    Product Place of Origin
    1 Cordoba, Argent
    2 Buenos Aires
    3 Sta.Fe.
    4 Santa Fe, AR 200
    5 Buenos Aires – Argentina 200
    6 Campana, BS.AS.
    7 Avellaneda,BS.AS.
    8 Cordoba – Argentina

    The targetColumnName is ‘Place of Origen’

    The ReplacementsTable, a table named CleanupCities, mostly has characters to replace:

    Text ReplaceWith
    , Argentina “”
    ,Argentina “”
    – Argentina “”
    ,AR 200 “”
    Sta.Fe. Santa Fe
    – Argentina 200 “”
    ,BS AS , Buenos Aires
    ,BS.AS. , Buenos Aires

    // Note that their are spaces needed for clean replacement.
    // Many of the Replace With fields are blank because we want those characters replaced by nothing.

    The desired output is:

    Product Place of Origin
    1 Cordoba
    2 Buenos Aires
    3 Santa Fe
    4 Santa Fe
    5 Buenos Aires
    6 Campana, Buenos Aires
    7 Avellaneda, Buenos Aires
    8 Cordoba

    I am setting the following:

    originalTable: Data
    targetColumnName: Place of Origen
    valuePairs: CleanupCities – I tried entering ‘CleanupCities’ and ‘[CleanupCities]’ with the same result.

    It’s probably an error with my syntax but I can’t figure it out.

    Thanks in advance for your help.

    Reply

  28. This solution works on a small sample I created. Thank you for the functionalized version of the query code as well!
    Would it possible for you to improve this code so that it is not case sensitive? As in replace “Air”, “air” or “AIR” with “water”?

    Reply

Leave a Reply