cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
rlmeyer
Resolver I
Resolver I

Using results of one query to feed and iterative query

I could use some help creating an iterative query using results from a REST API. The API lists the first 100 records but in order to go to the next 100 records it requires the the use of the Query Parameter "After" from the last record. For Example I need the query to look like this:

 

 

 

https://website_api/records?first=100  
https://website_api/records?first=100&after=WyIwN2RmYWI3NC01YzFhLTNkNmMtZWRiZi1lOWEyOWRhZDZkYWIiXQ==
https://website_api/records?first=100&after=WyIxMWJhZjE0NC05ZGNiLWZlMjItYzRhYy1hYTYyOWUyYjU2NTIiXQ== 

 

 

 

 

In this case my API call tells me my total number of records is 3020, so with 100 records each, I'll need it to iterate 30 times. I figured out how to calcuate the correct number of iterations based on the records and list them out in the format above.

 

I am stuck on the part where I run the first query, get the last "end_cursor" value, then dynamically feed that value to the next query and so one. Can anyone point me in the right direction.

1 ACCEPTED SOLUTION
rlmeyer
Resolver I
Resolver I

I ended up creating a function in order to accomplish this. Example in the link below. Would like to be to run the function as a loop instead of listing each step but it currently works.

Function that Repeats or Loops Through API Call Da... - Microsoft Power BI Community

View solution in original post

2 REPLIES 2
rlmeyer
Resolver I
Resolver I

I ended up creating a function in order to accomplish this. Example in the link below. Would like to be to run the function as a loop instead of listing each step but it currently works.

Function that Repeats or Loops Through API Call Da... - Microsoft Power BI Community

lbendlin
Super User
Super User

You can use List.Generate to get the URLs and then Table.AddColumn to retrieve the data.  That would make each http call twice but this should be covered by your web browser engine cache without much impact. It also avoids all the complexity of List.Accumulate or recursive functions.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors