Forum Discussion
Loop REST API with List.Generate with continuation token
Hi,
I have Power Query script below, but am returning empty list in the Loop step and suspect I am not understanding List.Generate correctly. The API is described here
let
Source = Xml.Tables(Web.Contents(Server,[RelativePath = GetAnalytics,Query = [path = Path, limit = Limit],Headers = [Authorization = "apikey " & Key]])){0}[Table],
ChangeType = Table.TransformColumnTypes(Source, {{"IsFinished", type logical}}),
Token = Table.FirstValue(Source),
Result = if ChangeType{0}[IsFinished] = true then {ChangeType} else
let
Loop = List.Generate(
() => [Result = fn_Next(Token)], // get table from function
each [Result][IsFinished] = false, // continue while IsFinished = false
each [Result = fn_Next(Token)], // continue by running function again with no change
each [Result] // give me my results
),
CombinedData = {Loop, ChangeType}
in
CombinedData
in
Result
The function fn_Next as follows:
(token as text) as table =>
let
Next = Xml.Tables( Web.Contents(Server,[RelativePath = GetAnalytics, Query = [token = (token), limit = Limit], Headers = [Authorization = "apikey " & Key]])){0}[Table],
TypeNext = Table.TransformColumnTypes(Next, {{"IsFinished", type logical}})
in
TypeNext
The API makes a first call and returns the following. If more results are available, then IsFinished = false:
| ResumptionToken | IsFinished | ResultXml |
| xxxx | FALSE | Table |
To get more results, I tried looping fn_Next to send the ResumptionToken until IsFinished = true (the token does not change). fn_Next returns:
| IsFinished | ResultXml |
| FALSE | Table |
At the moment, I'm getting an empty list from the Loop, but I know there are more results. Am I misunderstanding how to use List.Generate?
6 Replies
Looks like your actual data may be a sub-element of [Result]. Unless [IsFinished] is repeated for every record?
Thanks for your reply. Just to check I understand what you're asking: fn_Next returns IsFinished everytime until true. The actual data are in ResultXml -- I'm attempting to generate a list of all the results of fn_Next, then keep only the ResultXml's. Am I using List.Generate correctly in this scenario? Apologies if I'm misunderstanding your comment.
Your code looks a little off. Not sure what
CombinedData = {Loop, ChangeType}
is supposed to do. Create a list of a list and a table ?
Does your first call (before the List.Generate) produce data ?
As you can probably appreciate it is nearly impossible to help with API queries without access to said API (which you may not be willing to provide for understandable reasons)
