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
mvbenz
Frequent Visitor

List.Generate for API Calls

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...

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

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
Administrator
Administrator

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.

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.

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