List generation not stopping : pagination without knowing the number of pages

Hi Anonymous ,

it is not easy to debug a List.Generate from seeing the code alone, but you could try the following:

<!-- 
    Source = List.Generate(()=>
        [Result = try AuditHistoryFn(1) otherwise null, Page=1, PrevOData = 1],
        each [PrevOData]<>null,
     each [Result = try AuditHistoryFn([Page]+1) otherwise null, Page=[Page]+1],
           PrevOData = {Result][@odata.nextLink]
     each [Result]) 
    -->

So your function call never return an error and therefore Result will never get null.
Instead, you now drill down into the returned result instead and retrieve the previous odata.nextLink. That will hopefully keep the row number 7.

Hi Anonymous, I know you have set this to Solved, but I have been on a similar journey to yours, and I think I may have reached an alternative solution which is closer to Curbal's original using 'List.Generate()'.
I am working with the Atlassian Confluence Cloud api (ref: https://developer.atlassian.com/cloud/confluence/rest/v1/api-group-space/#api-wiki-rest-api-space-get)

The first part is to create a function which returns a table of results:

= (Page as number)=>

let
    Source = Json.Document(Web.Contents("https://<site_name>.atlassian.net/wiki/rest/api/space?expand=permissions&limit=25&start="&Number.ToText(Page))),
    #"Converted to Table" = Record.ToTable(Source),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"results", type any}, {"start", Int64.Type}, {"limit", Int64.Type}, {"size", Int64.Type}, {"_links", type any}}),
    values = #"Changed Type"{0}[results],
    #"Converted to Table1" = Table.FromList(values, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table1"

In my query with List.Generate(), I am then able to use this condition to test when to stop:
each Table.RowCount([Result])>0

(to my understanding, this returns FALSE when the table is empty. There may be other ways of getting the same result, but this one works for me)

It looks very simple, but it took me a few attempts to get there!
(inspiration came via this page https://www.dalesandro.net/handling-paginated-rest-api-results-with-power-query/)

let
    Source = List.Generate(()=>
    [Result = try fGetConfluenceDataTable(0) otherwise null, Page=0], 
    each Table.RowCount([Result])>0,
each [Result = try fGetConfluenceDataTable([Page]+25) otherwise null, Page=[Page]+25],
each [Result]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Column1"}, {"Column1.1"}),
    #"Expanded Column1.1" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.1", {"id", "key", "name", "type", "permissions", "status", "_expandable", "_links", "avatarUrls", "projectCategory", "projectTypeKey", "simplified", "style", "isPrivate", "properties"}, {"id", "key", "name", "type", "permissions", "status", "_expandable", "_links", "avatarUrls", "projectCategory", "projectTypeKey", "simplified", "style", "isPrivate", "properties"})
in
    #"Expanded Column1.1"

best regards,
Richard

Hi ImkeF, many thanks for responding to my question. Unfortunately that doesn't seem to do the trick. The BIccountant site has a ton of amazing content 🙂 I will keep digging into this and share the solution when I figure it out