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

[Anonymous]

TLDR: Stop generating list if the contents inside the list are empty. Empty lists are inside a table that has the lists.

list < table < list

Hello, I am trying to extract data from the URL, and I found that something works for me. Many thanks to Curbal's excellent video on How to do Pagination without knowing the number of pages (Part 2) in Power Query | List.Generate

Even though the script runs perfectly, the list.generate function generates rows without stopping. As you can see in the screenshot, it keeps generating even if the list contents are empty. The next page link is an indicator as well(this is an OData next page option which I am not using. I am using a page number parameter instead to get the set of records) 

Please see the Power Query steps with the results 

1.  Source (the script that's used)

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

Result

• #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
• #"Expanded Column1" 
   As you can see, there are many rows generated even if the list contents are empty. I am trying to generate the list until it hits the first null (it needs to include the first null as that list has a record).

Except for the list row 7, every other list does not contain any data.

The funtion that takes the page number as a parameter.

Really appreciate any guidance on this. I am not sure whether it makes sense to check the list.contains > table.contains > and again if the last list is empty to stop generating list. 

Thank you. 

Cheers, 
BK

[richard_brown]

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

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

[teste11234]

omg omg... this help me a lot.

many many tks.

[Anonymous · Accepted solution]

Hi @ImkeF, 

Many thanks for all your help. However debugging the list.generate() was taking so much time I decided to take a different path and ended up coming up with the following solution which worked. 

et 
    BaseUrl = "URL"
    EntitiesPerPage = 1000,
 
    GetJson = (Url) =>
        let Options = [
        Headers = [
          Accept="application/json",
          Prefer="odata.maxpagesize=1000"
        ]
      ],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let Url   = BaseUrl & "&$count=true",
            Json  = GetJson(Url),
            Count = Json[#"@odata.count"]
        in  Count,
 
    GetPage = (Index) =>
    
        let Skip  = "&$skiptoken=<cookie%20pagenumber=%22" & Number.ToText(Index) & "%22/>",
            //Top   = "$top=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Skip,
            Json  = GetJson(Url)
            //Value = Json[#"value"]
        in  Json,
 
    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
        PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 1 .. PageCount - 0 },
    Pages = List.Transform(PageIndices, each GetPage(_)),

This wouldn't have been possible if not for the post by @MarioB84 

https://community.powerbi.com/t5/Power-Query/Power-Query-Rest-API-Paging-with-odata-nextLink/td-p/19...

Thank you. 
Cheers, 
BK

[Anonymous]

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

[ImkeF]

Hi @Anonymous ,
next thing I would try is to move the stop-condition into the regular results for easier debugging. 
Then add a condition to run the loop 9 times and examine the results. You should see error-messaged indicating what is wrong with the stop-condition:

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

Make sure to expand all the columns for being able to debug

[ImkeF]

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.