Excel Query - Web.Contents function un-encoding '%2F' to a '/' when using GET request

Gazmeist 6 Reputation points
2021-10-31T11:13:04.047+00:00

Please see this archived question > https://social.technet.microsoft.com/Forums/ie/en-US/e40671d1-d6e1-47a8-8026-b9f163ef144c/webcontents-function-unencoding-2f-to-a-when-using-get-request?forum=powerquery

I have the same issue running a web query in Excel - the above indicates the issue has been resolved in Power BI in 2018, Why is it still an issue in Excel?

I have a paramaterized url that is calling an API for information, and the url should remain intact

URL= Parameter{0}[URLRaceDetails],  
Source = Json.Document(Web.Contents(URL)),  

Note - this trick doesn't work > Text.Replace(Uri.EscapeDataString(URL), "%2F", "%252F")

As the encoding is taking place when .Net Framework is retrieving the URL contents

The URL is https://api.beta.tab.com.au/v1/tab-info-service/racing/dates/2021-11-02/meetings/R/Randwick%20(NSW)/races/RANDWICK%20R01%2002%2F11?jurisdiction=NSW&fixedOdds=true

But the error message then indicates it's been encoded to

DataSource.Error: Web.Contents failed to get contents from 'https://api.beta.tab.com.au/v1/tab-info-service/racing/dates/2021-11-02/meetings/R/Randwick%20(NSW)/races/RANDWICK%20R01%2002/11?jurisdiction=NSW&fixedOdds=true' (404): Not Found  
Details:  
    DataSourceKind=Web  
    DataSourcePath=https://api.beta.tab.com.au/v1/tab-info-service/racing/dates/2021-11-02/meetings/R/Randwick%20(NSW)/races/RANDWICK%20R01%2002/11  
    Url=https://api.beta.tab.com.au/v1/tab-info-service/racing/dates/2021-11-02/meetings/R/Randwick%20(NSW)/races/RANDWICK%20R01%2002/11?jurisdiction=NSW&fixedOdds=true  

Here;s an earlier report from 2015 that logged it as a bug - still not resolved? https://social.msdn.microsoft.com/Forums/en-US/2e71ea5b-5edb-4d52-b610-a7ff7b41d4da/webcontents-uriescapedatastring-parser-error-pq-converts-2f-to-but-shouldnt?forum=powerquery

Please please please fix this (or point me to the fix) as it's driven me crazy for years now.

Thanks

Just an addendum - since the above url may not work (given date specificity) in a few days - below is the a snippet of the JSON response to confirm the original url is correct
145234-capture.jpg

Community Center | Not monitored
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.