List.Generate: missing last item

Question

Hello,

What am I trying to do
I'm creating a PowerBI report that retrieves data from the Azure Devops REST APIs about builds and releases (using a Personal Access Token to authenticate). Those APIs use pagination and return at most 100 records. Their response also includes an HTTP header containing a continuation token containing the first id of the next page. That id can be passed in to the next call in order to retrieve the next page.

My goal is to retrieve all releases of the past 5 days which may be one or more pages.
I use the List.Generate function to loop over the pages:

let
                // Only retrieve pages containing data from this date and newer.
                OldestDate = #date(2021,6,19),
                PageSize = 100,

                Source = List.Generate(() => [PageNr=0, Output=GetPageForReleases(PageSize, OldestDate, 0)],
                               each [Output][IsLastPage]=false,
                               each [PageNr=[PageNr]+1, Output=GetPageForReleases(PageSize, OldestDate, [Output][Page][LastId]+1)],
                               each [NestedList=[Output][Page]]
                )
in
    Source

Problems
The header for the continuation token is a custom header (x-ms-continuationtoken) and is not available in the metadata of the request in PowerBI. My workaround is to just compute it based on the last ID of the previous page (LastId +1. This works well.

The PowerQuery routine above works well except that its output does not include the last page. When pageSize is 100, if there are 106 releases available, the output still is a list containing a single item: the first page containing 100 items. The last 6 are missing.

The strange thing is that the algoritm does indeed properly determine that it's finished. That tells me that the subroutine that retrieves the actual page was indeed involved for that last page and did do its job (returning IsLastPage=true).

I did find one post of a similar issue:
[https://www.thebiccountant.com/2020/05/15/miss-last-page-paging-power-bi-power-query/][1]
The workaround is not useful in my case since the Azure Devops API does not return the link to the next page and even if it did, I use a custom algoritm to see if I want to retrieve it: keep retrieving while data on the page is newer than the specified date.

I hope anyone can help me to get that last row back as well.

Kind regards,
Jeroen

Answer 1

Your condition "each [Output][IsLastPage]=false" is filtering out the last page. Try modifying your code so the condition is only true for the pages (including the last one), and false for anything beyond that point.

EDIT: it seems logical to think of List.Generate as generating the next value and including it in the result if condition is true. But when condition is true, List.Generate actually includes the current value in the result and then generates the next value. But the next value will only be included in the result if it also satisfies condition.