XLOOKUP Not Finding Associated Value After Cleaning Up Data With Power Query

Question

I am trying to find a file that is associated with a link using XLOOKUP. In the source document I am using, the links have hex characters in the URL and the file name listed in the report does not. I'm using Power Query to replace the hex characters with the actual characters so they can match the file names. For example, %20 will be replaced with a space.

The issue I'm running into right now is that replacing %C2%A0 with a space is causing problems when running the XLOOKUP. There is an associated file with the link but it is resulting in a not found.

Link: website.com/Example%20File%C2%A0Name%C2%A0Services.pdf

After Power Query cleanup: website.com/Example File Name Services.pdf

File name: website.com/Example File Name Services.pdf

I tried to create dummy data but was unable to replicate my issue so that leads me to believe that the issue lies with my source document, which is autogenerated and emailed out by IT. I cannot share that document so I'm hoping there is some checks or fixes I can do to solve this issue.

I've attached the dummy data document to show what I am trying to accomplish and my Power Query code to see if anything is wrong there.

Also, if there is a better way to consolidate the Links steps, please let me know!

lorenzo · Answer

renee_crozier&nbsp;&nbsp;All the hard work by&nbsp;SergeiBaklan, just posting a couple of alternatives for the UTF replacement (in attached file)&nbsp;// Variant1
  ReplacedUtf = Table.ReplaceValue( Source, each [LinkURL], null,
      (old_value, current_value, replacer) as text =&gt;
          if current_value is null then old_value else
          List.Accumulate( ListReplacmentsTexts, current_value,
              (value,lists) =&gt; 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) =&gt; Text.Replace( current_value , lists{0}, lists{1} )
           ),
      Replacer.ReplaceText,
      {"LinkURL"}
  )&nbsp;

sergeibaklan · Answer

renee_crozier&nbsp;
As a comment, not to replace UTF-8 codes one by one, you may take encoding table from here&nbsp;HTML URL Encoding Reference&nbsp;using Power Query
let
    Source = Web.BrowserContents("https://www.w3schools.com/tags/ref_urlencode.asp?bcsi-ac-4d57fec82d0c41f9=271918E500000005GWA6BeQ6JifHv9+eq2S/b7a4YSJMAAAABQAAAHXHRgCAcAAAAAAAAAGYAAA="),
    ExtractTable = Html.Table(
        Source,
        {
              {"Column1", "TABLE.ws-table-all.notranslate:nth-child(36) &gt; * &gt; TR &gt; :nth-child(1)"}
            , {"Column2", "TABLE.ws-table-all.notranslate:nth-child(36) &gt; * &gt; TR &gt; :nth-child(2)"}
            , {"Column3", "TABLE.ws-table-all.notranslate:nth-child(36) &gt; * &gt; TR &gt; :nth-child(3)"}
        }, [RowSelector="TABLE.ws-table-all.notranslate:nth-child(36) &gt; * &gt; TR"]
    ),
    PromotHeaders = Table.PromoteHeaders(
        ExtractTable,
        [PromoteAllScalars=true]),
    SelectColumns = Table.SelectColumns(
        PromotHeaders,
        {"From UTF-8", "Character"}),
    ReplaceSpace = Table.ReplaceValue(
        SelectColumns,
        "space",
        " ",
        Replacer.ReplaceText,{"Character"}),
    ReplaceEmptyOnSpace = Table.ReplaceValue(
        ReplaceSpace,
        "",
        " ",
        Replacer.ReplaceValue,{"Character"}),
    RenameColumns = Table.RenameColumns(
        ReplaceEmptyOnSpace,
        {{"From UTF-8", "UTF-8"}})
in
    RenameColumns
or by any other way. Having such table replacements could be done as
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

ListReplacmentsTexts = List.Zip(
        {
           #"ASCII Encoding Reference"[#"UTF-8"],
           #"ASCII Encoding Reference"[Character]
        }
    ),

ReplaceTexts = Table.TransformColumns(
        Source,
        {
            {"LinkURL",
            each List.Accumulate(ListReplacmentsTexts,_,(x,y)=&gt;Text.Replace(x,y{0},y{1})),type text}
        }
    )

in
    ReplaceTexts
Please check in attached file.

sergeibaklan · Answer

renee_crozier&nbsp;
Sorry, I didn't catch what is the problem with XLOOKUP. You have two URL:s, in each of them you replaced UTF-8 codes %20 (space) and %C2%A0 (non-breaking space) on " " (ASCII space). As result you have two exactly the same texts.
After that you XLOOKUP such text on list of URL:s. XLOOKUP correctly returns first found one.

renee_crozier · Answer

That is correct. The problem is that the XLOOKUP is not finding a match, even though they are exactly the same. Using the Dummy Data attachment, imagine Sheet2 cell B2 results in #N/A. If I copy A2 from Table1 tab into Sheet2 cell A2, the XLOOKUP will find the match even though the replaced value is the same. I hope that makes sense

sergeibaklan · Answer

renee_crozier&nbsp;
Sorry, I didn't catch, what exactly XLOOKUP can't find? Could you please share example with the text which returns N/A but you expect another result.

Forum Discussion

XLOOKUP Not Finding Associated Value After Cleaning Up Data With Power Query

Share

Resources