Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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*
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.
Best regards,
Yuliana Gu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |