Help Needed - Paginating and Parameterizing a POST API call

[Young_Tunafish]

Hi all - I'm truly stumped on this problem. I have experience paginating and inserting dynamic parameters in GET calls, but it appears POST API calls in Power BI require a different method. Here is my 3-variable function in the dataflow making a POST call:

(page as number, startingdate as text, endingdate as text)=>
let
  url = "https://fakeurl.com/v1/",
  api_consumer_key = "fake-key",
  authorization = "basic fakeauthodwklsjlsdjafkljasd",
  clientId = "0495",
  header = [#"api-consumer-key" = api_consumer_key, #"authorization" = authorization, #"Content-Type" = "application/json; charset = utf-8"],
  content = Json.FromValue([
      action = "fetchQuery",
      domainObject = [#"page" = Number.ToText(page), #"startDate" = startingdate, #"endDate" = endingdate, #"client" = [#"id" = clientId]]]),
  response = Web.Contents(
      url,
      [
          Headers = header,
          Content = content
      ]
  ),
  jsonResponse = Json.Document(response)
in
  jsonResponse

To deal with Gateway timeout (the server taking too long to respond to large calls), I am forced to shift to more calls with less volume. I addressed this by iterating the function over narrow start and end dates. I then added the pagination with List.Generate as the page loads are too small for even a narrow date range. The following function iterates across a 2 column table of [START DATE] and [END DATE]:

let
  Source = #"Post API Date Parameters ETL",
  #"Added fx" = Table.AddColumn(Source, "fxMediaFrames", each let 
      PageList = List.Generate(()=>
         [Result = try fxMediaFrames(1, [START DATE], [END DATE]) otherwise null, page = 1],
         each not List.IsEmpty ([Result]),
         each [Result= try fxMediaFrames([page]+1, [START DATE], [END DATE]) otherwise null, page = [page]+1],
         each [Result])
     in PageList)
in
  #"Added fx"

When I run this iterated function, I receive this vague error:

When I manually enter the 3 variables to the POST call, I successfully retrieve a single page of records, as expected. The error arises when I attempt to paginate and parameterize the call within the [START DATE] and [END DATE] columns.

If you made it this far, any help on the function or insight on the error would be hugely appreciated. Thanks all.

[Anonymous]

Hi,@Young_Tunafish 

Regarding the issue you raised, my solution is as follows:
1.First of all, according to the error message you provided, it's probably because you used the function with the wrong parameter selection, you can check whether the required parameters of the function meet the rules.

Here is the link to the documentation I found:

MediaFrameReference Class (Windows.Media.Capture.Frames) - Windows UWP applications | Microsoft Lear...
2.Secondly, check if your API has a rate limit. Consider whether the API you are calling has a rate limit that could cause a Gateway timeout error. If so, introducing delays between calls or reducing the number of concurrent requests may help alleviate the problem.

3.Finally, if the error message you provided has "Go to error" as shown in the figure below, you can also click the link to troubleshoot the error.

If you have any new findings, we welcome you to share them with us.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Young_Tunafish]

Thank you for the insight, Leroy. Notes below: 

1. I just happened to name the function fxMediaFrames, but the data is really just URLs.  I do not believe the wrapper you linked applies.  The parameters are correct as I can return data when each variable is called explicitly without paging the call.
2. I inserted the Function.InvokeAfter to delay the calls to account for a rate limit, per the code below.  The same error persists.

(page as number, startingdate as text, endingdate as text)=>
let
  url = "https://fakeurl",
  api_consumer_key = "fake-key",
  authorization = "basic fakeauthorization32905840945890",
  clientId = "0495",
  header = [#"api-consumer-key" = api_consumer_key, #"authorization" = authorization, #"Content-Type" = "application/json; charset = utf-8"],
  content = Json.FromValue([
        action = "fetchQuery",
        domainObject = [#"page" = Number.ToText(page), #"startDate" = startindate, #"endDate" = endingdate, #"client" = [#"id" = clientId]]
    ]),
  response = Function.InvokeAfter(() => Web.Contents(
        url,
        [
            Headers = header,
            Content = content
        ]
    ), #duration(0,0,0,2)),
  jsonResponse = Json.Document(response)
  in 
  jsonResponse

    3. There is no Go To Error button available.  There are no rows returned following the pagination, just the error message I attached in my original post.

Please let me know if you have any other leads, thank you!