Forum Discussion
Anonymous
8 years agoNot applicable
Change text in table based on column in another table
Hi, I have a table which contains one column with text in each row. Additionally I have a second table, this table contains one column with one word in each row. All these words should be deleted...
- 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
MarcelBeug
Community Champion
8 years agoIn 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
Anonymous
8 years agoNot applicable
Hi Marcel,
thanks a lot for your solution! It works like expected.
BR