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
      Super 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
      Super 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])),