Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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...
  • MarcelBeug's avatar
    MarcelBeug
    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