Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
HI @murrayb8,
I guess below blog will suitable for your requirement:
Multi Condition Logic In Power Query
Regards,
Xiaoxin Sheng
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |