Appending API query to current table, or creating a table on refresh (self made incremental refresh)

[jamie_nourish]

I have created a table that fetches from an API with pages. This works well and it loops over the pages and can build the table.

It's taking over 2 hours for it to fetch from the one endpoint.

As the latest data doesn't really change, what I am trying to do in Query Editor is to fetch the data once and then append the data in a table. Incremental refresh is not working as the fields are not in the endpoint and it is not my endpoint.

So far my thinking and what I need help with is, the Query should:

1. Fetch the current table for argument say 10 records
2. Go to the api and fetch the next batch (10) records
3. Combine that, and output the table of 20 records
   
   

Each time I run the query I want it to add another 10 to the current table, but all I keep getting is a cyclic error. I don't seem to be able to work it out, even with a function.

Is there a way to run a query and create a new table, dynamically?

Any ideas appreciated

[v-yingjl · Accepted solution]

Hi @jamie_nourish ,

You can use List.Generate() to try your loop query and then append the list like:

let
...
TablesToAppend = List.Generate(your loop query),
TablesAppended = Table.Combine(List.Transform(TablesToAppend, each (_))),
...
in
...

Please refer:

1. Power Query - Loop through unknown number of Tables and append them all 
2. List.Generate() and Looping in PowerQuery 
3. Loop API request and append results to one table 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.