Replace Words in Original Text

=TRIM(REDUCE(" "&Original,SEQUENCE(ROWS(From)),LAMBDA(x,y,SUBSTITUTE(x," "&INDEX(From,y)," "&INDEX(To,y)))))

Excel solution 8 for Replace Words in Original Text, proposed by Viswanathan M B:

=Reduce(A2:A4, sequence(rows(c2:c7)),
 Lambda(a, b, substitute(a, index(c2:c7,b), index(d2:d7,b)))
 )

There is a table which has Original Text and there is another table which contains the list of words which need to be replaced. After replacement, output should look like Result. Updated on the basis of comments – To provide further clarity 1. It is word to word replacement. Hence, cat should be changed to Cat only if it finds cat not cats or catastrophe. 2. To keep problem simple, let’s limit punctuation marks to only comma, dot and apostrophe. For example cat, cat. cat’s The practice file has been updated with these test cases. Hence, there will be little difference between picture posted here and practice file.

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 9
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Replace Words in Original Text with Power Query

Power Query solution 1 for Replace Words in Original Text, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ListaOriginal = Table.TransformColumns(
    Source, 
    {
      {
        "Original Text", 
        each Text.Split(
          Text.Replace(Text.Replace(Text.Replace(_, ",", " ,"), ".", " ."), "'", " '"), 
          " "
        )
      }
    }
  ), 
  ListAccumulate = Table.TransformColumns(
    ListaOriginal, 
    {
      "Original Text", 
      each List.Accumulate(
        Table.ToRecords(Cambios), 
        _, 
        (S, C) => List.ReplaceValue(S, C[From], C[To], Replacer.ReplaceText)
      )
    }
  ), 
  Solution = Table.TransformColumns(
    ListAccumulate, 
    {
      "Original Text", 
      each Text.Replace(
        Text.Replace(Text.Replace(Text.Combine(_, " "), " ,", ","), " .", "."), 
        " '", 
        "'"
      )
    }
  )
in
  Solution

Power Query solution 2 for Replace Words in Original Text, proposed by Luan Rodrigues:

let
  Source = Excel.CurrentWorkbook(){[Name = "Tabela10"]}[Content], 
  Indice = Table.AddIndexColumn(Source, "Índice", 0, 1, Int64.Type), 
  List = Table.AddColumn(
    Indice, 
    "List", 
    each Text.Split(
      Text.Replace(Text.Replace(Text.Replace([Original Text], ",", " ,"), "'", " ' "), ".", " ."), 
      " "
    )
  ), 
  List_Exp = Table.ExpandListColumn(List, "List"), 
  Mesc = Table.NestedJoin(List_Exp, {"List"}, LookupTbl, {"From"}, "LookupTbl", JoinKind.LeftOuter), 
  Lkp = Table.ExpandTableColumn(Mesc, "LookupTbl", {"To"}, {"To"}), 
  Cond = Table.AddColumn(Lkp, "Personalizar", each if [To] = null then [List] else [To])[
    [Índice], 
    [Personalizar]
  ], 
  Group = Table.Group(
    Cond, 
    {"Índice"}, 
    {{"Contagem", each _, type table [Índice = number, Personalizar = text]}}
  ), 
  Sub = Table.AddColumn(Group, "Final Text1", each [Contagem][Personalizar]), 
  a = Table.TransformColumns(
    Sub, 
    {"Final Text1", each Text.Combine(List.Transform(_, Text.From), " "), type text}
  )[[Final Text1]], 
  Result = Table.AddColumn(
    a, 
    "Final Text", 
    each Text.Replace(Text.Replace(Text.Replace([Final Text1], " ,", ","), " ' ", "'"), " .", ".")
  )[[Final Text]]
in
  Result

Power Query solution 4 for Replace Words in Original Text, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "Data09"]}[Content], 
  Separators = " ,'.", 
  DataFromTo = List.Buffer(Table.ToRows(Excel.CurrentWorkbook(){[Name = "Data09b"]}[Content])), 
  Add_Final = Table.AddColumn(
    Source, 
    "Final Text", 
    each 
      let
        _Words = List.ReplaceMatchingItems(Text.SplitAny(_[Original Text], Separators), DataFromTo), 
        _Separators = Text.ToList(Text.Select(_[Original Text], Text.ToList(Separators)) & " "),  //Add extra space for last word 
        _Result = Text.Combine(List.Transform(List.Zip({_Words, _Separators}), Text.Combine))
      in
        Text.Trim(_Result)
  ), 
  Remove_Original = Table.RemoveColumns(Add_Final, {"Original Text"})
in
  Remove_Original

Power Query solution 5 for Replace Words in Original Text, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

Part 2:
 #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Original Text", type text}}),
 #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Original Text"}, LookUp, {"From"}, "LookUp", JoinKind.LeftOuter),
 #"Expanded LookUp" = Table.ExpandTableColumn(#"Merged Queries", "LookUp", {"To"}, {"LookUp.To"}),
 #"Added Conditional Column" = Table.AddColumn(#"Expanded LookUp", "Custom", each if [LookUp.To] = null then [Original Text] else [LookUp.To]),
 #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Index", "Custom"}),
 #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Index"}, {{"Final Text", each Text.Combine([Custom]," "), type text}}),
 #"Replaced Value3" = Table.ReplaceValue(#"Grouped Rows"," ,",",",Replacer.ReplaceText,{"Final Text"}),
 #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3"," .",".",Replacer.ReplaceText,{"Final Text"}),
 #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4"," '","'",Replacer.ReplaceText,{"Final Text"}),
 #"Removed Other Columns1" = Table.SelectColumns(#"Replaced Value5",{"Final Text"})
in
 #"Removed Other Columns1"

Power Query solution 6 for Replace Words in Original Text, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Original Text", type text}}),
 #"Replaced Value" = Table.ReplaceValue(#"Changed Type",","," ,",Replacer.ReplaceText,{"Original Text"}),
 #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","'"," '",Replacer.ReplaceText,{"Original Text"}),
 #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","."," .",Replacer.ReplaceText,{"Original Text"}),
 #"Added Index" = Table.AddIndexColumn(#"Replaced Value2", "Index", 1, 1, Int64.Type),
 #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Original Text", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Original Text"),

Power Query solution 7 for Replace Words in Original Text, proposed by Matthias Friedmann:

https://lnkd.in/eaP-sQsd

Power Query solution 8 for Replace Words in Original Text, proposed by Matthias Friedmann:

let
  Source = Excel.CurrentWorkbook(){[Name = "SentencesToReplace"]}[Content], 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Final Text", 
    each 
      let
        Words = Text.Split([Original Text], " "), 
        WordsToReplace = Record.FromTable(
          Table.RenameColumns(
            WordLookup, 
            {
              {Table.ColumnNames(WordLookup){0}, "Name"}, 
              {Table.ColumnNames(WordLookup){1}, "Value"}
            }
          )
        ), 
        Substitution = List.Transform(Words, each Record.FieldOrDefault(WordsToReplace, _, _))
      in
        Text.Combine(Substitution, " ")
  )[[Final Text]]
in
  #"Added Custom"

Power Query solution 9 for Replace Words in Original Text, proposed by Venkata Rajesh:

let
  Source = Original, 
  Output = Table.AddColumn(
    Source, 
    "Result", 
    each Text.Replace(
      Text.Combine(
        List.ReplaceMatchingItems(
          Text.Split(Text.Replace([Original Text], ".", " ."), " "), 
          List.Zip({Lookup[From], Lookup[To]})
        ), 
        " "
      ), 
      " .", 
      "."
    )
  )[Result]
in
  Output

Power Query solution 10 for Replace Words in Original Text, proposed by Sergei Baklan:

let
  Original = Excel.CurrentWorkbook(){[Name = "range"]}[Content], 
  source = Table.PromoteHeaders(Original, [PromoteAllScalars = true]), 
  Subs = Excel.CurrentWorkbook(){[Name = "subs"]}[Content], 
  FromTo = Table.PromoteHeaders(Subs, [PromoteAllScalars = true]), 
  seps = {" ", ",", ".", "!", "?", "'", "'s"}, 
  fromPref = List.Union(List.Transform(FromTo[From], (a) => List.Transform(seps, (q) => q & a))), 
  fromSuff = List.Union(List.Transform(fromPref, (a) => List.Transform(seps, (q) => a & q))), 
  toPref = List.Union(List.Transform(FromTo[To], (a) => List.Transform(seps, (q) => q & a))), 
  toSuff = List.Union(List.Transform(toPref, (a) => List.Transform(seps, (q) => a & q))), 
  replacer = List.Zip({fromSuff, toSuff}), 
  ReplaceTexts = Table.TransformColumns(
    source, 
    {
      {
        "Original Text", 
        each List.Accumulate(replacer, " " & _ & " ", (x, y) => Text.Replace(x, y{0}, y{1})), 
        type text
      }
    }
  ), 
  newHeader = Table.RenameColumns(ReplaceTexts, {{"Original Text", "Corrected Text"}}), 
  trim = Table.TransformColumns(newHeader, {{"Corrected Text", Text.Trim, type text}})
in
  trim

Power Query solution 11 for Replace Words in Original Text, proposed by Melissa de Korte:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Punctuators = { ".", ",", "?", "!" },
 Result = Table.TransformColumns( Source,{{"Original Text", each 
 [
 t = Text.Split( _, " "),
 r = Text.Combine(
 List.Transform(
 List.Zip(
 {
 List.ReplaceMatchingItems( List.Transform( t, (x)=> Text.Remove( Text.BeforeDelimiter( x, "'"), Punctuators)), Replacements ),
 List.Transform( t, (x)=> if Text.Contains( x, "'") then "'" & Text.AfterDelimiter( x, "'") else "" ),
 List.Transform( t, (x)=> Text.Select( x, Punctuators)) 
 }
 ), Text.Combine 
 ), " "
 )
 ][r], type text}}
 )
in
 Result


And for the Replacements query:

let
 Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
 ToList = Table.AddColumn(Source, "Custom", each Record.ToList( _ ))[Custom]
in
 ToList

Power Query solution 12 for Replace Words in Original Text, proposed by Sue Bayes:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Original Text", type text}}),
 #"Replaced Value" = Table.ReplaceValue(#"Changed Type",","," ,",Replacer.ReplaceText,{"Original Text"}),
 #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","'"," '",Replacer.ReplaceText,{"Original Text"}),
 #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","."," .",Replacer.ReplaceText,{"Original Text"}),
 Custom1 = Table.AddColumn(#"Replaced Value2", "Custom", each Text.Combine(
 List.ReplaceMatchingItems(
 Text.Split ([Original Text], " ") ,
List.Buffer (List.Zip (Table.ToColumns(LookUp)) ),
Comparer.Ordinal ),
" ")),
 #"Replaced Value3" = Table.ReplaceValue(Custom1," ,",",",Replacer.ReplaceText,{"Custom"}),
 #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3"," '","'",Replacer.ReplaceText,{"Custom"}),
 #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4"," .",".",Replacer.ReplaceText,{"Custom"}),
 #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value5",{"Custom"})
in
 #"Removed Other Columns"
 It works but there is definite room to progress.   this helped https://gorilla.bi/power-query/replace-values/

Power Query solution 13 for Replace Words in Original Text, proposed by Artur Pilipczuk:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Added Custom1" = Table.AddColumn(Source, "Custom", each List.Accumulate(
List.RemoveFirstN(List.Accumulate( Text.Split([Original Text]," "),{""},(a,b)=> List.Combine({a,{" "},{b}})),2),{},
(e,f)=> 
if Text.Contains(f,",") then e & List.RemoveFirstN( List.Accumulate(Text.Split(f,","),{""},(c,d)=> List.Combine({c,{","},{d}}) ),2)
else if Text.Contains(f,"'") then e & List.RemoveFirstN( List.Accumulate(Text.Split(f,"'"),{""},(c,d)=> List.Combine({c,{"'"},{d}}) ),2)
else if Text.Contains(f,".") then e & List.RemoveFirstN( List.Accumulate(Text.Split(f,"."),{""},(c,d)=> List.Combine({c,{"."},{d}}) ),2)
else e & {f})
),
 #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ReplacedValues", each Text.Combine( List.ReplaceMatchingItems([Custom],List.Zip({fReplacements[From],fReplacements[To]})))),
 #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"ReplacedValues"})
in
 #"Removed Other Columns"

f rReplacements table:
let
 Source = ( Excel.CurrentWorkbook(){[Name="fReplacements"]}[Content])
in
 Source

Artur

Power Query solution 14 for Replace Words in Original Text, proposed by Hristo Tsenov:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), 
  #"Split" = Table.SplitColumn(
    #"Added Index", 
    "Original Text", 
    Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 
    {"Text1", "Text2", "Text3", "Text4", "Text5"}
  ), 
  #"Unpivot" = Table.UnpivotOtherColumns(#"Split", {"Index"}, "Att", "Value"), 
  #"Custom" = Table.AddColumn(
    #"Unpivot", 
    "Custom", 
    each Text.Select([Value], {"a" .. "z", "A" .. "Z"})
  ), 
  #"Merge" = Table.NestedJoin(#"Custom", {"Custom"}, Table2, {"From"}, "Table2", JoinKind.LeftOuter), 
  #"Expand" = Table.ExpandTableColumn(#"Merge", "Table2", {"To"}, {"To"}), 
  #"Custom2" = Table.AddColumn(#"Expand", "Result", each if [To] = null then [Value] else [To]), 
  #"Sort" = Table.Sort(#"Custom2", {{"Index", Order.Ascending}, {"Att", Order.Ascending}}), 
  #"Custom3" = Table.AddColumn(
    #"Sort", 
    "Final Result", 
    each Replacer.ReplaceText([Value], [Custom], [Result])
  ), 
  #"Grouped Rows" = Table.Group(
    Custom3, 
    {"Index"}, 
    {{"Final Text", each Text.Combine([Final Result], " "), type text}}
  ), 
  #"Select Columns" = Table.SelectColumns(#"Grouped Rows", {"Final Text"})
in
  #"Select Columns"

Solving the challenge of Replace Words in Original Text with Excel

Excel solution 1 for Replace Words in Original Text, proposed by محمد حلمي:

=REDUCE(B3:B6,C3:C8,LAMBDA(a,d,
SUBSTITUTE(a,d,OFFSET(d,,1))))

Excel solution 2 for Replace Words in Original Text, proposed by 🇰🇷 Taeyong Shin:

=REDUCE(B3:B6,D3:D8,LAMBDA(a,v,REGEXREPLACE(a,"b"&v&"b",VLOOKUP(v,D3:E8,2,))))

Excel solution 3 for Replace Words in Original Text, proposed by Aditya Kumar Darak 🇮🇳:

= MAP(
 B3:B5,
 LAMBDA(
 a,
 LET(
 _splt,
 TEXTSPLIT(a, , " "),
 _delm,
 {"",".",",","'s"},
 TEXTJOIN(
 " ",
 TRUE,
 IFNA(
 XLOOKUP(
 _splt,
 TOCOL(D3:D8 & _delm),
 TOCOL(E3:E8 & _delm)),
 _splt)))))

Excel solution 4 for Replace Words in Original Text, proposed by Aditya Kumar Darak 🇮🇳:

= LET(
 Formula,
 LAMBDA(
 ME,
 o,
 r,
 n,
 IF(n < 1,
 o,
 ME(
 ME,
 SUBSTITUTE(o, INDEX(r, n, 1), INDEX(r, n, 2)),
 r,
 n - 1))),
 Formula(Formula, B3:B5, D3:E8, ROWS(D3:E8)))

Excel solution 5 for Replace Words in Original Text, proposed by Aditya Kumar Darak 🇮🇳:

= REDUCE(
 original,
 SEQUENCE(ROWS(replace)),
 LAMBDA(
 a,
 b,
 SUBSTITUTE(
 a,
 INDEX(replace, b, 1),
 INDEX(replace, b, 2))))

Excel solution 6 for Replace Words in Original Text, proposed by Duy Tùng:

=REDUCE(B3:B6,E3:E8,LAMBDA(x,y,SUBSTITUTE(x,@+D8:y,y)))

Excel solution 7 for Replace Words in Original Text, proposed by Bhavya Gupta:

=TRIM(REDUCE(" "&Original,SEQUENCE(ROWS(From)),LAMBDA(x,y,SUBSTITUTE(x," "&INDEX(From,y)," "&INDEX(To,y)))))

Excel solution 8 for Replace Words in Original Text, proposed by Viswanathan M B:

=Reduce(A2:A4, sequence(rows(c2:c7)),
 Lambda(a, b, substitute(a, index(c2:c7,b), index(d2:d7,b)))
 )

Solving the challenge of Replace Words in Original Text with Power Query

Solving the challenge of Replace Words in Original Text with Excel

Leave a Reply Cancel reply