Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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 c...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thank you Anonymous  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