List.Generate for API Calls

[mvbenz]

I am using a List.Generate to loop Web.Contents through an API call in a Data Flow. The API call is Soap based and returns the data as XML. When I run the API call, I have to supply a date range to query. 

In my data flow I have a table setup to first query the API using the desired date range and get the top 1 records which will have the Total Records to be returned for that date range. That is what I use as my List.Generate offset for looping (basically if there are 100k records, loop till no more).

What I am running into is when I turn on the Incremental Refreshes in the data flow to keep the last XX months of data and refresh the last 4 days of data, the refresh fails with a 502 Bad gateway error. As a test I hard coded a one month run and then a two month run in the functions and the one month passed but the two month failed just to make sure it wasn't the Incremental Refresh stuff causing it.

In reading up on this, it looks like this is not the API throwing the error but Power Query is, due to too much data (file size) being downloaded and accumulated before the table is processed. 

Again, I can get 1 months worth of data which is ~147k records but if i go to 2 months which is ~280k records, I get the 502 error.

I was searching for a way to maybe dump/append the contents of List.Generate to a table every say 10,000 records to clear it out if the issue is the amount of data List.Generate is accumulating but coming up empty.

What's weird is I have other API calls that accumulate more data than this using the same method and they work without issue and the only real difference other than the endpoint is those are json based.

I am open to all suggestions on how to crack this nut...

TIA

Mike...

[Syndicate_Admin]

https://learn.microsoft.com/en-us/power-query/handling-paging

[mvbenz]

Very interesting! Thnaks, I'll give it a look. Right now I am getting total record count and looping using List.Generate so I do a primer query and get record count, then let List.Generate loop using that count until no more records.

[Syndicate_Admin]

Instead of List.Generate  use Incremental refresh and size the partitions so that you stay under the limit (worst case use daily partitions that then roll up to month/quarter/year.

[mvbenz]

Not sure what you mean by daily partitions. The setting are to keep XX Days/Months/Qtrs/Years and refresh the last XX Days/MonthsQtrs/Year. I will be using the incremental refresh as I wanted to keep 5 qtrs and refresh the last 7 days so if I miss a cycle or two for whatever reason, I'm good to go.

I need to do a List.Generate because the API is limited to 1000 records per query. One Month is over 140k records so it needs to loop through all records until ihave them all.