Forum Discussion
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 ...
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
So, that worked with getting rid of the error but it's not replacing the values
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
Really, I don't see. Where do the strings in [LinkURL] come from, copy/paste from the web?
The entire report is auto generated and sent out from our IT department.
OK, so where are the following lists, in the other workbook???:
{ #"Encoding Reference"[#"From UTF-8"], #"Encoding Reference"[#"Character"] }