XLOOKUP Not Finding Associated Value After Cleaning Up Data With Power Query
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!
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.pdfAfter Power Query cleanup: website.com/Example File Name Services.pdfFile 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! Read More
