Forum Discussion

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

excel

Formulas and Functions

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

Brass 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