Excel Power Query for REST API with pagination

Viewed 107

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

1 Answers

This is difficult to debug without access to the API but I can spot a couple of mistakes.

 each [result_count=[Result][maxResults], Result = GetNextResult(result_count)],

According to Jira docs, maxResults is just the number of results per page so no need to track or adjust this property.

Try something like this.

   each [result_count= [result_count]+50, Result = GetNextResult(Text.From(result_count))],

Everything else looks ok to me.

Related