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...
Need Help
  • 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