question

Gazmeist-6616 avatar image
0 Votes"
Gazmeist-6616 asked Ehren commented

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

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


power-query-not-supported
capture.jpg (841.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You would need to somehow get Excel to use .NET 4.5 instead of 3.5. Do you have 4.5 installed on your computer? I'm checking internally to see if there's anything you can do here.

0 Votes 0 ·

Unfortunately there's no workaround in Excel for this issue at this time.

0 Votes 0 ·

0 Answers