Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Replace Values

Hello,   I have values in a column like the following: "Example&#36;_&#33;"   The "&#<digits>;" are HTML codes that needs to be decoded.   In the example, the result string must be: "Example$...
Need Help
  • zoloturu's avatar
    zoloturu
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • zoloturu's avatar
    zoloturu
    Memorable Member
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      zoloturu,

       

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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