Rest API call not returning data when including $skip

[dkpcr5]

Hi,

I'm trying to retrieve data from a REST API. When I exclude $skip in the call, the data returns without issue. But when I include $skip, the call seems to take 10-15 minutes before finally timing out. What am I doing wrong?

This call works, no problem:

let
Source = Json.Document(Web.Contents(
"https://url ",[RelativePath="x/y/z?$filter=Date%20eq%20%27"&DateTo&"%27%20and%20Type%20eq%20%27Base%27%20and%20Value%20ne%200%20&$top=50",
Headers=
        [   
            #"header1" = "header1",
            #"header2" = Text.Combine({header2}),
            #"response-cache" = "false",
            ]]
        )
)
in
    Source

But this one fails to load:

let
Source = Json.Document(Web.Contents(
"https://url ",[RelativePath="x/y/z?$filter=Date%20eq%20%27"&DateTo&"%27%20and%20Type%20eq%20%27Base%27%20and%20Value%20ne%200%20&$top=50&$skip=50",
Headers=
        [   
            #"header1" = "header1",
            #"header2" = Text.Combine({header2}),
            #"response-cache" = "false",
            ]]
        )
)
in
    Source

Again, both calls work just fine in Postman... What are some possible problems? Thanks!

[mahoneypat]

It is odd that it is working in Postman but now PQ, but you are packaging the Headers info differently.  You could try to look at Query Diagnostics to see the actual final URL generated/sent by Power Query to see if it matches your Postman url.  Is header2 a hard-coded list in your query?

You could also try moving $skip to be an earlier parameter vs at the end.

Pat

[dkpcr5]

Hey Pat, thanks for the response.

In the diagnostics, I see where the call times out. It's almost the same as the Postman call, except that at the end of the OData string, there's this:

HTTP/1.1
<Headers placeholder>

So in the BI diagnostic tool, the URL where things get jammed up looks like this:

GET https://url?$filter=Date eq '2021-09-30' and Type eq 'Base' and Value ne 0 &$count=true&$top=100&$skip=0 HTTP/1.1
<Headers placeholder>

Note: that "$skip=0" is coming from Column1 custom List.Numbers function.

In Postman (runs great), it looks like this:

GET https://url?$filter=Type eq 'Base' and Date eq '2021-09-30' and Value ne 0&$count=true&$top=50&$skip=50

So, there are some minor differences. But, maybe that HTTP/1.1 is relevant?

[dkpcr5]

@mahoneypat A few more details:

* No, none of the headers are lists. All headers used are either hard-coded in quotes or single-string parameters, type Text.

* In the Diagnostics_Detailed table (created from traces), I've sorted calls by Exclusive Duration. All of the most time-consuming calls (seen in the "Data Source Query" column) match what's in Postman, except for adding HTTP/1.1 <Headers placeholder>, as noted above. The "Additional Info" column shows a "Record" with further detail, including an Exception thrown. That Exception is reproduced below. It's clear that the failure is caused by a timeout, but I'm not sure how to learn more about precisely what's causing the timeout.

Again, any direction is appreciated. Thanks again for taking a look!

Exception:
ExceptionType: System.Net.WebException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=a12b3c456789d012
Message: The operation has timed out
StackTrace:
   at System.Net.HttpWebRequest.GetResponse()
   at Microsoft.Mashup.Engine1.Library.Common.WrappingHttpWebRequest.<>c__DisplayClass119_0.<WrapResponse>b__0()
   at Microsoft.Mashup.Engine1.Library.Common.WrappingHttpWebRequest.WrapExceptionResponse[T](Func`1 getValue)
   at Microsoft.Mashup.Engine1.Library.Web.Request.CreateResponse(ResourceCredentialCollection credentials)
   at Microsoft.Mashup.Engine1.Library.Web.Request.GetResponseCore(ResourceCredentialCollection credentials)
   at Microsoft.Mashup.Engine1.Library.Common.RetryPolicy.Execute[TResult](IEngineHost host, Func`1 func)
   at Microsoft.Mashup.Engine1.Library.Web.Request.GetResponse(ResourceCredentialCollection credentials, RetryPolicy retryPolicy, SecurityExceptionCreator securityExceptionCreator, Boolean tokenRefreshed)

[mahoneypat]

Not sure what else to try.  One thing would be to try the string with HTTP in Postman to see if that times out.  I did see this in the documentation.

@ImkeF Any ideas or workarounds?

Pat

[ImkeF]

Sorry, but no idea here.