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

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

renee_crozier
Brass Contributor
Nov 08, 2024
So, that worked with getting rid of the error but it's not replacing the values
- Lorenzo
  Silver Contributor
  Nov 08, 2024
  renee_crozier
  Trying to guess where the problem comes from as this works no problem on my side, at least for the few records where I have 1 or more %20