If statements and RelativePath conversion for Web

[simonwking]

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

[simonwking · Accepted solution]

I believe I have it working now using,

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

[simonwking]

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]

Unfortunately without access to that API I cannot help more.

[simonwking]

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]

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

[lbendlin]

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