Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
murrayb8
Helper I
Helper I

Need help bulk replacing substrings contained within text based on translation table

Hi,

 

I have been trying to find a way to bulk find and replace substrings of text based on a replacement table but I've had no luck getting a solution to work so far and need a bit of help I think.

 

My Scenario:

I have 2 tables of data, the first table we'll say has multiple columns with one text column that I want to operate on and the second table has two columns, one with substrings to find in the text and the second with what to replace the substring with.

 

So far the best two options I have found are here: https://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-i... which seems to only make a substring replacement once(in my scenario the substring could occur multiple times in the text), and this article: https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/ which seems to feed in a list of words, as my substrings wont reliably be delimited by any character I'll need to recursively search for the substring until it doesnt exist then move on I presume. 

 

So far I have tried pasting the below into the advanced editor of the table where my text sits but when the new column is created I'm getting error as the cell contents and the error message says that [Text] cannot be found:

ReplacementFunction([Text]))

 

 

 

let

Source = #"FACT Table",
Replacements = #"TranslationTable", TextToChange = #"FACT Table", WordsToReplace = Table.Column(Replacements, "SubstringToFix"), WordsToReplaceWith = Table.Column(Replacements, "CorrectSubstring"), ReplacementFunction = (InputText)=> let DoReplacement = List.Generate( ()=> [Counter=0, MyText=InputText], each [Counter]<=List.Count(WordsToReplaceWith), each [Counter=[Counter]+1, MyText=Text.Replace( [MyText], WordsToReplace{[Counter]}, WordsToReplaceWith{[Counter]})], each [MyText]), GetLastValue = List.Last(DoReplacement) in GetLastValue, Output = Table.AddColumn(TextToChange, "Changed Text", each ReplacementFunction([Text])) in Output

 *Edited to update the query im using*

1 REPLY 1
v-yulgu-msft
Employee
Employee

Hi @murrayb8,

 

Per my understanding, you should type the actual column name that you  want to change in your fact table, as the input parameter of ReplacementFunction, rather than "[Text]". "Text" is the column name used in the author's example.

 

1.PNG

 

Best 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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.