I have an Excel (15225.20356) Power Query that pulls data from a REST API. I have no trouble getting the first page of data (50 results). But, I'm struggling with writing the for-loop to generate a list with the full results.
The JSON I receive from the first query looks like this:
{
"maxResults": 50,
"startAt": 0,
"isLast": false,
"values": [...]
}
I'm trying to achieve something like this C code in Power Query
struct Results {
int maxResults;
int startAt;
BOOL isLast;
Values values;
};
Results result;
List list;
for(int result_count = 0;
result.isLast == FALSE;
result_count += result.maxResults) {
try {
result.startAt = result_count;
GetNextResult(&result);
list.insert(&result); // taking some liberties in the translation from PQ to pseudo-C here
}
catch (...) {
break;
}
}
My attempt in Power Query looks like this
let
GetNextResult=(start as number) =>
let
Source = Json.Document(Web.Contents("https://my.jira.server/rest/agile/latest/board/8479/sprint",[Query=[startAt=Number.ToText(start)]]))
in
Source,
GetAllResults=()=>
let
AllResults = List.Generate(()=>
[result_count=0, Result = GetNextResult(0)],
each [Result][isLast] <> false,
each [result_count=[Result][maxResults], Result = GetNextResult(result_count)],
each [Result][values])
in
AllResults
in
GetAllResults
This results in an empty list. How do I generate a results list from multiple queries to the REST API?
Thanks