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

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

Forum Discussion

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

Resources