Forum Discussion

renee_crozier's avatar
renee_crozier
Copper Contributor
Nov 08, 2024

Trying to Find/Replace Based on Another Sheet in Power Query

I have a list of URLs that have UTF-8 characters in them and I need to replace all of the UTF-8 characters with the actual characters (i.e. %26 to &).

 

I've found two code samples from a separate thread to help me with this but I'm having trouble targeting the correct column (LinkURL) and am getting an error:

The two code samples referenced above:

// Variant1
  ReplacedUtf = Table.ReplaceValue( Source, each [LinkURL], null,
      (old_value, current_value, replacer) as text =>
          if current_value is null then old_value else
          List.Accumulate( ListReplacmentsTexts, current_value,
              (value,lists) => Text.Replace( value , lists{0}, lists{1} )
          ),
      {"LinkURL"}
  )

// Variant2
  ReplacedUtf = Table.ReplaceValue( Source,
      each if [LinkURL] is null then false else [LinkURL],
      each List.Accumulate( ListReplacmentsTexts, [LinkURL],
              (current_value,lists) => Text.Replace( current_value , lists{0}, lists{1} )
           ),
      Replacer.ReplaceText,
      {"LinkURL"}
  )

 

This is the sheet with all of the encoding characters in it that I'm trying to reference:

 

Full code for the AllContentReport as found in the Advanced Editor:

 

Column I am trying to target

 

I've tried switching out LinkURL in the code above to Column19 since the promoted headers step hadn't been executed yet but that didn't work either.

 

Due to the nature of this content, I'm unable to share the workbook that I'm working with and, since it's referencing a connected workbook rather than a sheet found in this workbook, I didn't think creating a dummy data spreadsheet would help here.

 

Any help would be greatly appreciated!

excel
Formulas and Functions

14 Replies

Sort By
  • Mks_1973's avatar
    Mks_1973
    Iron Contributor
    Nov 08, 2024

    use this modified version of your code that might work with the Table.TransformColumns approach:

    let
        Source = Excel.Workbook(File.Contents("C:\Source Documents\AllContentReport.xlsx"), null, true),
        AllContentReport_Sheet = Source{[Item="AllContentReport",Kind="Sheet"]}[Data],
        ListReplacementsTexts = List.Zip({#"Encoding Reference"[#"From UTF-8"], #"Encoding Reference"[Character]}),

        // Ensure headers are promoted if not done already
        PromotedHeaders = Table.PromoteHeaders(AllContentReport_Sheet, [PromoteAllScalars=true]),

        // Replace UTF-8 characters in LinkURL column
        ReplacedUtf = Table.TransformColumns(
            PromotedHeaders,
            {{"LinkURL", each List.Accumulate(ListReplacementsTexts, _, (current, lists) => Text.Replace(current, lists{0}, lists{1}))}}
        )
    in
        ReplacedUtf


     

    Ensure ListReplacementsTexts is correctly structured with each item as { "encoded_value", "decoded_value" }.

     

    Example: ListReplacementsTexts = { { "%20", " " }, { "%21", "!" }, { "%22", "\"" }, ... }



    Also:
    Ensure that the LinkURL column is accessible in the query by checking the column names at each step. It appears you initially referenced LinkURL but also tried Column19. Verify that the correct column name is in place after any renaming or transformation steps.

    If the headers are not yet promoted, Power Query will not recognize LinkURL as the column name. Add a Promote Headers step before attempting to reference LinkURL

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 08, 2024

    Hi renee_crozier 

    Almost mission impossible to help you with what you shared. The error message you get says it all: The [LinkURL] column table doesn't exist in your table. And that's not the only problem. To help you I need 2 things at least:

    • A picture showing [Column19] with couple of more rows. Need to see what's below "LinkURL" to make sure that's what is supposed to be treated by the ReplacedUtf step
    • Post the code (not picture) of your query code (remove the file path no problem)

    Thanks

    • renee_crozier's avatar
      renee_crozier
      Copper Contributor
      Nov 08, 2024

      Apologies.

      Code:

      let
    Source = Excel.Workbook(File.Contents("\Source Documents\AllContentReport.xlsx"), null, true),
    AllContentReport_Sheet = Source{[Item="AllContentReport",Kind="Sheet"]}[Data],
    ListReplacmentsTexts = List.Zip(
        {
           #"Encoding Reference"[#"From UTF-8"],
           #"Encoding Reference"[#"Character"]
        }
    ),
    ReplacedUtf = Table.ReplaceValue(Source, each [LinkURL], null,
      (old_value, current_value, replacer) as text =>
          if current_value is null then old_value else
          List.Accumulate( ListReplacmentsTexts, current_value,
              (value,lists) => Text.Replace( value , lists{0}, lists{1} )
          ),
      {"LinkURL"}
    )
in
    ReplacedUtf

      Few extra rows in column 19

       

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Nov 08, 2024

        renee_crozier 

        This should do it:

        let
    Source = Excel.Workbook(File.Contents("\Source Documents\AllContentReport.xlsx"), null, true),
    AllContentReport_Sheet = Source{[Item="AllContentReport",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(AllContentReport_Sheet, [PromoteAllScalars=true] ),
    ListReplacementsTexts = List.Zip(
        {
           #"Encoding Reference"[#"From UTF-8"],
           #"Encoding Reference"[#"Character"]
        }
    ),
    ReplacedUtf = Table.ReplaceValue(PromotedHeaders, each [LinkURL], null,
      (old_value, current_value, replacer) as text =>
          if current_value is null then old_value else
          List.Accumulate( ListReplacementsTexts, current_value,
              (value,lists) => Text.Replace( value , lists{0}, lists{1} )
          ),
      {"LinkURL"}
    )
in
    ReplacedUtf

         

Resources