Replacing text , exact

[Dicken]

Hi, 
I have been experimenting with ways of replacing multiple text values, but taking into account 'exact' replace and 
also the problem of punctuation, so my start table; 

= #table( type table [ A = text , B  = text], 
{{ "the cat and dog walked around the cattle", 
  "the cat's milk and the dog's bone" }})

and I want to replace 'cat and "dog"   so have  'oldnew' ;

= {{"cat","TIGER"}, {"dog","WOLF"}}

so my fist method; 

 List.Accumulate( oldnew, atable , (s,c)=> 
 Table.TransformColumns( s, 
 List.Transform( Table.ColumnNames( atable ), (x)=> { x, each Text.Replace( _ , c{0} , c{1}) } )))

so this is great for the puntuaton, but not for exact as 'cattle' = "TIGERtle' , not what is wanted; , so then 

Table.TransformColumns( atable, 
List.Transform( Table.ColumnNames( atable) , (x)=> 
 { x,  each Text.Combine( 
List.ReplaceMatchingItems( 
Text.SplitAny( _," .,'""?!"),  oldnew )," ")   } ))

so this works, but now we have the punctuation problem  so dog's = WOLF not   WOLF's,  
so i thought; 

 Table.AddColumn(atable, "Custom", each 
let token = 
Text.SplitAny( [A], """' ,.!?") , ptest = 
List.Transform( token, (x)=> if x = "s" then  "'"&x else 
 x ), rplace  = List.ReplaceMatchingItems( ptest, 
 {{"cat", "TIGER"}, {"dog","WOLF"}} ) in 
Text.Combine( rplace , " ") )

but not sure how good this is really, , so does anyone have ideas about other functions or techniques to deal with this,
other than  just doing it in excel with regex ☺️

Richard. 

[Dicken]

Have not gone through all of that but it looks pretty comprehensive, i don't reallly know any python, should learn some, so this could be the impetus to do sp. 

[ronrsnfld · Accepted solution]

I can think of two methods to do this.

1. Use a Python script to enable a Regex Replace method. You can use the word boundary token to ensure you only match words.

let
    pairs_table = Table.FromColumns({{"cat","dog"}, {"TIGER","WOLF"}},
      type table[Find=text,Replace=text]),
    Source = #table( type table [ A = text , B = text],
      {{ "the cat and dog walked around the cattle",
      "the cat's milk and the dog's bone" }}),
    #"Run Python script" = Python.Execute("#(lf)import re#(lf)import pandas as pd#(lf)#(lf)def build_pattern(pairs):#(lf)    words = sorted((p[0] for p in pairs), key=len, reverse=True)#(lf)    escaped = [re.escape(w) for w in words]#(lf)    return re.compile(r'\b(' + '|'.join(escaped) + r')\b', flags=re.IGNORECASE)#(lf)#(lf)def regex_replace_words(value, pairs, lookup, pattern):#(lf)    if pd.isna(value):#(lf)        return value#(lf)    def sub_fn(m):#(lf)        return lookup[m.group(0).lower()]#(lf)    return pattern.sub(sub_fn, str(value))#(lf)#(lf)pairs = list(pairs_table.itertuples(index=False, name=None))#(lf)lookup = {find.lower(): repl for find, repl in pairs}#(lf)pattern = build_pattern(pairs)#(lf)#(lf)output = dataset.copy()#(lf)output['A'] = output['A'].apply(lambda v: regex_replace_words(v, pairs, lookup, pattern))#(lf)output['B'] = output['B'].apply(lambda v: regex_replace_words(v, pairs, lookup, pattern))",
      [dataset = Source, pairs_table = pairs_table]),
    output = #"Run Python script"{[Name="output"]}[Value]
in
    output

2. Split the sentence into word and non-word groups, and apply the replacement only on the words.

let
    pairs_table = Table.FromColumns({{"cat","dog"}, {"TIGER","WOLF"}},
        type table[Find=text, Replace=text]),

    Source = #table(type table [A = text, B = text],
        {{ "the cat and dog walked around the cattle",
           "the cat's milk and the dog's bone" }}),

    // Build a case-insensitive lookup: record field name = lowercase find-word
    Lookup = Record.FromList(pairs_table[Replace],
        List.Transform(pairs_table[Find], Text.Lower)),

    //WordChars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_",
    //IsWordChar = (c as text) as logical => Text.Contains(WordChars, c),

    // Split a string into alternating word / non-word runs
    WordChars = List.Combine({{"a".."z"}, {"A".."Z"}, {"0".."9"}, {"_"}}),

    // Split at word→nonword transitions, then split those pieces at nonword→word
    Tokenize = (s as text) as list =>
        List.Combine(
            List.Transform(
                Splitter.SplitTextByCharacterTransition(WordChars, (c) => not List.Contains(WordChars, c))(s),
                Splitter.SplitTextByCharacterTransition((c) => not List.Contains(WordChars, c), WordChars))),
     ReplaceWords = (value as nullable text) as nullable text =>
        if value = null then null
        else Text.Combine(
            List.Transform(Tokenize(value), each
                Record.FieldOrDefault(Lookup, Text.Lower(_), _)), ""),


    #"Replaced Text" = Table.TransformColumns(Source,
        {{"A", ReplaceWords, type text}, {"B", ReplaceWords, type text}})
in
    #"Replaced Text"

If you have a relatively small database, I'd use the M Code version; if you have hundreds of thousands or more strings to process, I'd probably use the python version

Here is the result given your data: