Forum Discussion
Replace Values
Hello,
I have values in a column like the following:
"Example$_!"
The "&#<digits>;" are HTML codes that needs to be decoded.
In the example, the result string must be:
"Example$_!"
The HTML codes can be of course any valid code.
I have already a Web Query with the list of code and its equivalent character.
What is the best way to do the decoding in my case and replace the values with Power Query?
Thanks!
Jonathan
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!
9 Replies
Hi Anonymous,
How many such symbols your text cell can contain? Two or a different amount?
Regards,
RuslanIn power query under Transform menu, you will have to use Split by Delimiter and Replace Values a few times to get to your wanted result.
If you have different scenarios, meanining you have to apply different steps based on the value, then you could try to duplicate the column then apply the different steps acctodringly.
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!
Hi Anonymous
Is the result string should be:
"Example$_!" for all rows though code string are varies in rows?
If so, follow steps below
Split column
Add custom column
Merge column
Best Regards
Maggie
The result needs to vary for each row. In fact, the source strings vary, each can have between 0 and n HTML codes of the form "&#<digits>;". Each of these must be replace with their corresponding character.
So unfortunately, your solution won't solve my problem.
Thank you
