Forum Discussion
How to create a function to search for and replace strings based on another table?
Hi, I'm trying to find a way to search for and replace multiple strings in a column of text dynamically using values from another table but im getting stuck. My data exists in two tables, one table, "Raw data", with the column of text strings, these are essentially sentences, and the other table, "Replacements", reads in from an excel file and contains two columns, one with the text to find and the other with the text to replace.
I found what looks to be a good post but I cant get it to work for me here: https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/
I've essentially gone into my Replacements table and in advanced editor I've tried to ammend in the function from the site above but I'm missing something as I get an error at the TurnTextToList section stating that a type List cant be converted to type Table.
Here is my code so far:
let
Source = Excel.Workbook(File.Contents("C:\Users\OneDrive\Sentences.xlsx"), null, true),
Replacements_Table = Source{[Item="Replacements",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Replacements_Table,{{"Text to Replace", type text}, {"Text to Add", type text}}),
CreateListOfLists = Table.AddColumn(Source, "Liste", each ({[Text To Replace], [Text to Add]})),
Text = Sentences[Original Text],
TurnTextToList = Table.AddColumn(Text, "Custom", each Text.Split([Text], " ")),
Replacements = Table.AddColumn(TurnTextToList, "Changed Text Expected",
each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste])," ")),
Cleanup = Table.RemoveColumns(Replacements,{"Custom", "Text"})
in
Cleanup
Any Help someone could give to help me get this working would be really appreciated!
Thanks
2 Replies
- AnonymousNot applicable
Hi, murrayb8.
Not sure on the M side, since I typically do this on the DAX side. You can either use Substitute or Replace.
Best,
Scott - AnonymousNot applicable
HI murrayb8,
I guess below blog will suitable for your requirement:
Multi Condition Logic In Power QueryRegards,
Xiaoxin Sheng