Forum Discussion
Change text in table based on column in another table
- 8 years ago
In this case, the challenges/questions are:
1. To isolate words, so only complete words are replaced.
2. After replacing words by blanks, multiple delimiters may result.
In the solution below, all words are replaced by blanks, and also the delimiter directly following those words are removed.
Replacements are done case insensitive.
let Source = Table1, Delimiters = Text.ToList(" ,':;,.!?"), ReplacementList = List.Transform(Table2[Word], each {_, ""}), SplittedText = Table.AddColumn(Source, "Words", each Splitter.SplitTextByAnyDelimiter(Delimiters)([Word cloud]), type {text}), ReplacedWords = Table.AddColumn(SplittedText,"Replaced Words", each List.ReplaceMatchingItems([Words],ReplacementList,Comparer.OrdinalIgnoreCase), type {text}), AddedDelimiters = Table.AddColumn(ReplacedWords, "Delimiters", each Text.ToList(Text.Select([Word cloud],Delimiters))&{""}, type {text}), AddedWordsWithDelimiters = Table.AddColumn(AddedDelimiters, "WordsWithDelimiters", each List.Transform(List.Select(List.Zip({[Words],[Replaced Words],[Delimiters]}),each not (_{0} <> "" and _{1} = "")), each _{1} & _{2}), type {text}), AddedNewWordCloud = Table.AddColumn(AddedWordsWithDelimiters, "New Word cloud", each Text.Trim(Text.Combine([WordsWithDelimiters])), type text), RemovedColumns = Table.RemoveColumns(AddedNewWordCloud,{"Word cloud", "Words", "Replaced Words", "Delimiters", "WordsWithDelimiters"}), RenamedColumns = Table.RenameColumns(RemovedColumns,{{"New Word cloud", "Word cloud"}}) in RenamedColumns
In this case, the challenges/questions are:
1. To isolate words, so only complete words are replaced.
2. After replacing words by blanks, multiple delimiters may result.
In the solution below, all words are replaced by blanks, and also the delimiter directly following those words are removed.
Replacements are done case insensitive.
let
Source = Table1,
Delimiters = Text.ToList(" ,':;,.!?"),
ReplacementList = List.Transform(Table2[Word], each {_, ""}),
SplittedText = Table.AddColumn(Source, "Words", each Splitter.SplitTextByAnyDelimiter(Delimiters)([Word cloud]), type {text}),
ReplacedWords = Table.AddColumn(SplittedText,"Replaced Words", each List.ReplaceMatchingItems([Words],ReplacementList,Comparer.OrdinalIgnoreCase), type {text}),
AddedDelimiters = Table.AddColumn(ReplacedWords, "Delimiters", each Text.ToList(Text.Select([Word cloud],Delimiters))&{""}, type {text}),
AddedWordsWithDelimiters = Table.AddColumn(AddedDelimiters, "WordsWithDelimiters", each List.Transform(List.Select(List.Zip({[Words],[Replaced Words],[Delimiters]}),each not (_{0} <> "" and _{1} = "")), each _{1} & _{2}), type {text}),
AddedNewWordCloud = Table.AddColumn(AddedWordsWithDelimiters, "New Word cloud", each Text.Trim(Text.Combine([WordsWithDelimiters])), type text),
RemovedColumns = Table.RemoveColumns(AddedNewWordCloud,{"Word cloud", "Words", "Replaced Words", "Delimiters", "WordsWithDelimiters"}),
RenamedColumns = Table.RenameColumns(RemovedColumns,{{"New Word cloud", "Word cloud"}})
in
RenamedColumns
Greetings MarcelBeug,
if we need to REPLACE text from the 1st table with values from the 2nd table (so the 2nd table would now have another column with the text to find and replace from the 2nd column), would that be possible in M?