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!