question

jbjbjb avatar image
0 Votes"
jbjbjb asked Lz-3068 commented

List.Generate: missing last item

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

power-query-not-supported
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

For completeness I attached the code of the GetPageForReleases function for invoking the Azure Devops API to get the releases in Power BI.116753-getpageforreleases.txt


0 Votes 0 ·

1 Answer

Ehren avatar image
1 Vote"
Ehren answered Lz-3068 commented

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.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@jbjbjb

If you don't mind could you post the code that solves the issue on your side following Ehren's response?
Thanks in advance

0 Votes 0 ·