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

     

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.