List.Generate API Pagination based on rel="next"

[MarkusEng1998]

I am using list.generate to seed the [skip] & [top] values for the URI pagination. I have to guess how many records will be returned, e.g. if I know there are 1000+ records, I set the list.generate condition [skip]< 1100.

When the response header no longer returns rel="next" then there are no more records. Where do I include this?

Here is my initial code where I manually guess the pagination values. The last step of Table.ExpandListColumns I find the last record [dRofusRecord] = null.

let
	
    Source = #"200Rooms",
    #"01filterProj" = Table.SelectRows(Source, each ([projName] = projectFilter)),
    #"10listGenerate100" = Table.AddColumn( #"01filterProj", "dRofusRecord", each List.Generate(() =>
    [skip = 0,  top = 10 ],
        each [skip] <= rmSkip,   // rmsSkip is my guess of the record number.
        each [skip = [skip] + [top], 
      top = [top] ]
)),
    #"11expandList" = Table.ExpandListColumn(#"10listGenerate100", "dRofusRecord"),
    #"12expandDrofusRecord" = Table.ExpandRecordColumn(#"11expandList", "dRofusRecord", {"skip", "top"}, {"skip", "top"}),
    #"13changeType" = Table.TransformColumnTypes(#"12expandDrofusRecord",{{"skip", Int64.Type}, {"top", Int64.Type}}),
    
    // API call
    #"20getProjURI" = Table.AddColumn(#"13changeType", "drofusRecord", each getProjectURIpaginated([top], [skip])),

    #"21expandProjURI" = Table.ExpandListColumn(#"20getProjURI", "drofusRecord")
in
    #"21expandProjURI"

Here is the getProjectURIpaginated()

(topVal as number, skipVal as number)=>
let
    #"FilterColumnTable" = Table.SelectRows(#"111APIitemsA1", each ([ObjectType] = "items")),
    GetWebContents =
        Web.Contents(
            "https://api-us.drofus.com/api/company/",
            [
                RelativePath= "143"
                & "/items" 
                & "?$select=" & Text.Combine(#"FilterColumnTable"[dRofus.ID], ",")
                & "&$top=" & Number.ToText(topVal)
                & "&$skip=" & Number.ToText(skipVal) 
            ]
        ),            
    GetBody = Json.Document(GetWebContents)    
in
    GetBody

I have seen videos where I incorporate getProjectURIpaginated() into the list.generate function, but get confused.

Thank you for any help.

[MarkusEng1998 · Accepted solution]

Thank you @v-yiruan-msft  for your help! I decided to take a U-Turn and rethink the solution how to end the API pagination automatically. Fortunately, I found a post by @lbendlin using List.IsEmpty().

I revised the List.Generate() section to include the List.Empty().

= Table.AddColumn(#"01filterProject", "dRofusRecord", each List.Generate(() =>
[projNo = [projNo], skip = 0,  top = 50, recordList = getURI(projNo, top, skip)],

each not List.IsEmpty([recordList]),
each [projNo = [projNo],
      skip = [skip] + [top], 
      top = [top],
       recordList = getURI(projNo, top, skip)]
))

getURI()

(projNo as text, topVal as number, skipVal as number)=>
let
    #"FilterColumnTable" = Table.SelectRows(#"102APImapRooms100", each ([ObjectType] = "rooms")),
    GetWebContents =
        Web.Contents(
            "https://api-us.drofus.com/api/company/",
            [
                RelativePath= projNo
                & "/rooms" 
                & "?$select=" & Text.Combine(#"FilterColumnTable"[dRofus.ID], ",")
                & "&$top=" & Number.ToText(topVal)
                & "&$skip=" & Number.ToText(skipVal) 
            ]
        ),
    convertedJson = Json.Document(GetWebContents,65001),     
        in
            convertedJson

[v-yiruan-msft]

Hi @MarkusEng1998 ,

I’m delighted to hear that your issue has been resolved. Thank you for sharing the solution with our community. Your contribution could prove invaluable to other users who might encounter the same problem. Your efforts are greatly appreciated!

Best Regards

[MarkusEng1998]

GetHeaders() without the GetHeaders script. Results of GetWebContent.

[MarkusEng1998]

Thank you @v-yiruan-msft , I will give this a try.

[v-yiruan-msft]

Hi @MarkusEng1998 ,

Please make a littile adjustment as below on your codes and check if it can return your expected result...

1. Make modification on List.Generate() part

let
    Source = #"200Rooms",
    #"01filterProj" = Table.SelectRows(Source, each ([projName] = projectFilter)),
    #"10listGenerate" = Table.AddColumn(#"01filterProj", "dRofusRecord", each List.Generate(
        () => [skip = 0, top = 10],
        each [skip] <= rmSkip and GetHeaders([top], [skip]),
        each [skip = [skip] + [top], top = [top]]
    )),
    #"11expandList" = Table.ExpandListColumn(#"10listGenerate", "dRofusRecord"),
    #"12expandDrofusRecord" = Table.ExpandRecordColumn(#"11expandList", "dRofusRecord", {"skip", "top"}, {"skip", "top"}),
    #"13changeType" = Table.TransformColumnTypes(#"12expandDrofusRecord",{{"skip", Int64.Type}, {"top", Int64.Type}}),
    #"20getProjURI" = Table.AddColumn(#"13changeType", "drofusRecord", each getProjectURIpaginated([top], [skip])),
    #"21expandProjURI" = Table.ExpandListColumn(#"20getProjURI", "drofusRecord")
in
    #"21expandProjURI"

2. Change the code for the function getProjectURIpaginated()

  GetHeaders = (topVal as number, skipVal as number) =>
        let
            #"FilterColumnTable" = Table.SelectRows(#"111APIitemsA1", each ([ObjectType] = "items")),
            GetWebContents =
                Web.Contents(
                      "https://api-us.drofus.com/api/amazon/",
                    [
                        RelativePath= "143"
                        & "/items" 
                        & "?$select=" & Text.Combine(#"FilterColumnTable"[dRofus.ID], ",")
                        & "&$top=" & Number.ToText(topVal)
                        & "&$skip=" & Number.ToText(skipVal) 
                    ]
                ),
            GetHeaders = Record.FieldOrDefault(GetWebContents, "Headers", []),
            LinkHeader = Record.FieldOrDefault(GetHeaders, "Link", ""),
            RelNext = Text.Contains(LinkHeader, "rel=next")
        in
            RelNext

Best Regards

[MarkusEng1998]

Thank you for the code for GetHeaders(). When I invoke this function, it returns a cannot convert binary to type record. I think this error is between GetWebContents (returns a JSON file) and GetHeaders (returns Binary error).

I can bypass this error by converting the JSON, which returns a list of records. The GetHeaders is expecting a different input. revised GetHeaders()

(topVal as number, skipVal as number)=>
let
    #"FilterColumnTable" = Table.SelectRows(#"102APImapRooms100", each ([ObjectType] = "rooms")),
    GetWebContents =
        Web.Contents("https://api-us.drofus.com/api/amazon/",
            [
                RelativePath= "01" & "/rooms" 
                & "?$select=" & Text.Combine(#"FilterColumnTable"[dRofus.ID], ",")
                & "&$top=" & Number.ToText(topVal) & "&$skip=" & Number.ToText(skipVal) 
            ]
        ),
    convertedJson = Json.Document(GetWebContents,65001),
    convertedList = Table.FromList(convertedJson, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
 convertedRecord = Record.FromTable(convertedList),  
     GetHeaders = Record.FieldOrDefault(convertedRecord, "Headers", []),
            LinkHeader = Record.FieldOrDefault(GetHeaders, "Link", ""),
            RelNext = Text.Contains(LinkHeader, "next")
    
        in
            GetHeaders

error message at convertedRecord step.

If I skip this step GetHeaders error: cannot convert value of type Table to type Record.