Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors