List.Generate API Pagination Data Load returning more rows

[Ivano1]

Hello. I have set up a parameter called fTickets to get a page of 100 results from an API by entering a page number. I also have a query that also uses the list.generate command to loop through pages until a page does not have any results and returns null using the parameter. This is how my query looks

Query1:

= List.Generate(()=>
[Result= try fTickets(1) otherwise null, Page=1],
each [Result]<>null,
each [Result= try fTickets([Page]+1) otherwise null, Page=[Page]+1],
each [Result])

This works fine in Power Query and I can see my results and it stops at 1000 rows (80 are null 920 have data), but when I Close & Apply to load the data to power bi dashboard, it begins to load more rows than in the original query. Power Bi desktop will load the query and I had to cancel when it got to over 3000 rows. Im not sure what the issue might be but hoping for some help

Thank you!

[ppm1]

How much data do you expect? at 100/page, 30 pages doesn't sound unreasonable. If you hard code your url into a browser and manually increase the page #, when do you stop seeing data?

Also, usually those REST APIs offer a TOP or LIMIT term so you can override the default of 100, so you can get your data in fewer calls.

Pat

[Ivano1]

Thank you for your response, there is currently only 920 records of data, the rest returns Null. The API is pulling from a tickets database so its always adding records as people submit tickets to our helpdesk but currently there are 920 records of data. So when I see the power bi desktop adding anything over 920 rows its all null data which I don't need. 

The default is 10 records per api call so I increased that to 100 records per page. 

[ppm1]

Thanks for the additional information. The issue is likely that your [Result] is never null. You may be getting an empty record, but that is not the same as null. You likely need to evaluate something within [Result] in your condition. On one of those 80 extra rows in the preview, click in the cell to the right of the word Record to see what is returned below. From there, you can come up with a condition.

Pat

[Ivano1]

When I put the link into my browser for a known page number that I know will not have results, I get the following:

{"tickets":[]}

In power query, any row that doesnt contain data past the 920 records all shows null values for all columns. Which I have around 120 columns of data for other records. I am guessing power query sees the {"tickets"}:[] and thinks there is data so it does not stop the loop? I have 0 experience with M functions so I don't know what condition would stop the loop.

I appreciate your help so far.

[ppm1]

Try 

each [Result][tickets]<>null

as your condition.

Or you can try

each List.Count([Result][tickets])>0

Pat

[Ivano1]

Thank you, ive tried both examples given and they both return the same behavior. Rows 1-920 are populated with data, and 921-1000 are all null columns. If I scroll past 1000 rows it keeps adding 50 rows of null data at a time indefinitely. 

I had to change the [Result][tickets] to [Result][tickets.id] because I received an error that it could not find column tickets.