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

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

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

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-yingjl
Community Support
Community Support

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors