Forum Discussion
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 from the text in Table 1.
So I need the possibility to iterate through all the rows in Table 1. For each of these rows I need to go through all the words in Table 2, and if one occurence of the words is found in Table 1, it has to be replaced by blank. I think it has to be done with power query, but I havn´t found a way to solve this. The two tables
Do you maybe have an approach?
Thanks in advance!
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
4 Replies
v-yulgu-msftMicrosoft Employee
Hi Anonymous,
After testing, I din't find a valid way to replace [word] esisting in another table either using DAX or Power Query.
MarcelBeug Maybe other experts in Power Query have some ideas.
Regards,
Yuliana Gu
- MarcelBeug
Community Champion
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