topic Re: List.Generate API Pagination based on rel="next" in Developer

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

MarkusEng1998 — Fri, 03 May 2024 17:01:09 GMT

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.

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

Anonymous — Wed, 01 May 2024 02:19:21 GMT

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

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

MarkusEng1998 — Wed, 01 May 2024 13:52:47 GMT

Thank you @Anonymous , I will give this a try.

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

MarkusEng1998 — Wed, 01 May 2024 20:01:48 GMT

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.

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

MarkusEng1998 — Wed, 01 May 2024 16:04:42 GMT

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

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

MarkusEng1998 — Fri, 03 May 2024 17:35:48 GMT

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

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

Anonymous — Mon, 06 May 2024 01:26:41 GMT

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