cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simonwking
Regular Visitor

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

1 ACCEPTED SOLUTION
simonwking
Regular Visitor

I believe I have it working now using,

 

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

View solution in original post

6 REPLIES 6
simonwking
Regular Visitor

I believe I have it working now using,

 

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

simonwking
Regular Visitor

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.

Unfortunately without access to that API I cannot help more.

simonwking
Regular Visitor

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,

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
Super User
Super User

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors