Forum Discussion
Replace Values
Hi Anonymous,
In this case, the best solution will be a recursive custom function in Power Query.
1. Create a new blank query and paste below code there:
(InputText as text)=> //Text variable //Generate a list of consecutive replacements and get only the last value List.Last(List.Generate( ()=> [ //Set default values for counter and result column MyText=InputText, nr = Text.Length(InputText) - Text.Length(Text.Replace(InputText,"&#","#")) ], each [nr]<>0, //Condition each [ //Result MyText = let //Get first occurrence of &#<digit>; combination Code = Number.FromText(Text.BetweenDelimiters([MyText], "&#", ";")), //Get related text to be replaced to based on result form previous row TextNew = Table.SelectRows(Dictionary, each [Column1] = Code)[Column2]{0}, //Do replace Result = Text.Replace([MyText],"&#" & Number.ToText(Code) & ";",TextNew) in Result, nr = Text.Length([MyText]) - Text.Length(Text.Replace([MyText],"&#","#")) ], each [MyText]) )2. Go to your query with a column to be replaced and then Press menu Add Column -> Invoke Custom Function
Enter a name for the new column and select Function query (from step 1). Then choose a column to be replaced and press OK.
RegExp replace
dictionary
I've learned this approach from Chris Webb's post - https://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in-text-in-power-query/. Maybe you will find it useful as well.
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
Anonymous,
Yes, I know I can do it, but I think it won't work if the count of symbols varies on each cell content. Let's say in the first cell I have 2 symbols in the string but on the next I have 3 symbols. If I am not wrong, applying the steps as you described won't solve my problem.
Thanks
Hi Anonymous,
In this case, the best solution will be a recursive custom function in Power Query.
1. Create a new blank query and paste below code there:
(InputText as text)=> //Text variable
//Generate a list of consecutive replacements and get only the last value
List.Last(List.Generate(
()=> [ //Set default values for counter and result column
MyText=InputText,
nr = Text.Length(InputText) - Text.Length(Text.Replace(InputText,"&#","#"))
],
each [nr]<>0, //Condition
each [ //Result
MyText =
let
//Get first occurrence of &#<digit>; combination
Code = Number.FromText(Text.BetweenDelimiters([MyText], "&#", ";")),
//Get related text to be replaced to based on result form previous row
TextNew = Table.SelectRows(Dictionary, each [Column1] = Code)[Column2]{0},
//Do replace
Result = Text.Replace([MyText],"&#" & Number.ToText(Code) & ";",TextNew)
in
Result,
nr = Text.Length([MyText]) - Text.Length(Text.Replace([MyText],"&#","#"))
],
each [MyText])
)2. Go to your query with a column to be replaced and then Press menu Add Column -> Invoke Custom Function
Enter a name for the new column and select Function query (from step 1). Then choose a column to be replaced and press OK.
RegExp replace
dictionary
I've learned this approach from Chris Webb's post - https://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in-text-in-power-query/. Maybe you will find it useful as well.
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
Thanks a lot for that superb reply! It sounds really good! I haven't had time to try it yet. I will let you know if it works after.
Thank you
Hi zoloturu,
You solution worked almost as is. I had to modify the code a little bit. One of the changes is to fix the case where the input string is not containing a HTML code. The function was returning null. Here is below the code of the function I use now. Thanks for the help!
(inputText as text) => let transformed = List.Last( List.Generate( () => [ MyText = inputText, nr = Text.Length(inputText) - Text.Length(Text.Replace(inputText, "&#", "#")) ], each [nr] <> 0, each [ MyText = let CodeOnly = Text.BetweenDelimiters([MyText], "&#", ";"), Code = "&#" & CodeOnly & ";", TextNew = Table.SelectRows(#"HTML codes", each [Numeric code] = Code)[Char]{0}, Result = Text.Replace([MyText], Code, TextNew) in Result, nr = Text.Length([MyText]) - Text.Length(Text.Replace([MyText], "&#", "#")) ], each [MyText])), result = if transformed is null then inputText else transformed in result