cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TorbenM
Frequent Visitor

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 tablesThe two tables

Do you maybe have an approach?

Thanks in advance!

1 ACCEPTED SOLUTION

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

 

Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
v-yulgu-msft
Microsoft
Microsoft

Hi @TorbenM,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Specializing in Power Query Formula Language (M)
iKiok
Frequent Visitor

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?

Hi Marcel,

 

thanks a lot for your solution! It works like expected.

 

BR

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!