Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

If statements and RelativePath conversion for Web

I am trying to convert some PowerBI which works on Desktop but not on Web.  It is trying to pull down a long list 100 items at a time, via API.  Based on whether its the first pass or a subsequent pass, move the cursor along to start downloading next batch.

 

Code below works but has a dynamic URL

 

FnGetMorePages =
(url, cursor, iter) as record =>
let
// Fetch "page 1" with 100 results on first pass. Then all other passes, grab subsequent pages.
// This logic deals with data sets less than 100 results nicely without errors, as the API errors with cursor=null
agentUrl =
if iter = 1
then url
else Text.Combine({url,"&cursor=",cursor}),
Source = Json.Document(Web.Contents(agentUrl, [Headers = headers])),
data = try Source[data] otherwise null,
next = try Source[pagination][nextCursor] otherwise null,
res = [Data=data, Next=next]
in
res,

 

trying to replace the agentUrl with [RelativePath=agentUrl] doesnt work so I tried breaking out the If statement

 

FnGetMorePages =
(url, cursor, iter) as record =>
query = if iter = 1 then
let
Source = Json.Document(Web.Contents("https://xxxxx.net/web/api/v2.1/agents?limit=100", [Headers = headers])),
data = try Source[data] otherwise null,
next = try Source[pagination][nextCursor] otherwise null,
res = [Data=data, Next=next],
in res
else
let
Source = Json.Document(Web.Contents("https://xxxxx.net/web/api/v2.1/agents?limit=100&cursor="&cursor, [Headers = headers])),
data = try Source[data] otherwise null,
next = try Source[pagination][nextCursor] otherwise null,
res = [Data=data, Next=next]
in res,

 

But his doesnt work.

 

Any help would be appreciated

  • Anonymous's avatar
    Anonymous
    3 years ago

    I believe I have it working now using,

     

    agents", [Query=[limit="100", cursor=cursor], Headers=headers])),

6 Replies

  • Both limit and cursor should go into the Query parameter, not the RelativePath parameter.

    There is no harm in calling it with a cursor pointing to the first record, so you don't really need to check for iter = 1

  • Anonymous's avatar
    Anonymous
    Not applicable

    Ive tried with Queries but it just fails. Even If I just do a limit of 100 in the query (and ignore cursor for now) if results in 'null' data  [Query=[limit="100"]]. The full Function is below 

     

    FnGetMorePages =
    (url, cursor, iter) as record =>
    let
    Source = Json.Document(Web.Contents("https://xxxxx.net/web/api/v2.1/agents", [Query=[limit="100", cursor=cursor]], [Headers = headers])),
    data = try Source[data] otherwise null,
    next = try Source[pagination][nextCursor] otherwise null,
    res = [Data=data, Next=next]
    in
    res,

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User

      might want to check the parameter format.  Not sure if Query is smart enough to handle integers and texts in the same URL string.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Yes as there is a firstpage call the cursor definately has a value. I have removed that if statement but the issue remains that the Source command with the &cursor does not work online. I have tried it as a Query or as a RelativePath but neither work.

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User

      Unfortunately without access to that API I cannot help more.

  • Anonymous's avatar
    Anonymous
    Not applicable

    I believe I have it working now using,

     

    agents", [Query=[limit="100", cursor=cursor], Headers=headers])),