Power Query List.Generate to loop paginated API calls-CodePudding

I am trying to append the results multiple API calls in Power Query to build an outcome table.

The API limits each call to 1000 rows, but each result returns the current page number as well as the next call and the total rows so it's easy to know what to iterate on and how many times. I need to start at page 1 and end at total rows / 1000.

I just need help with the syntax if it's possible to append each subsequent result in a loop with the cumulative previous calls.

Something like a List.Generate inside a function might work, but I'm out of my depth here.

CodePudding user response：

I have managed to answer my question using the reference in the comment from @horseyride.

It involved creating two custom functions in Power Query in M code. I'm sharing the important parts of both functions below.

fnGetResults:

    (page, outcome) =>
let
    url = "https://myapi.com/outcomes/findings/?outcome="&Number.ToText(outcome)&"&page="&Number.ToText(page)&"&page_size=1000",
    Source = Json.Document(Web.Contents(url, [Headers = [#"Authorization"="JWT "&get_token]])),
    results = Source[results]
in
    results

fnGetOutcomes:

    (id) =>
let
    outcome_list = List.Generate (
        () => [page = 1, result = fnGetResults(1,id)],
        each not List.IsEmpty([result]),
        each [page= [page]   1, result = fnGetResults([page],id)],
        each [result]
    ),
    #"Converted to Table" = Table.FromList(outcome_list, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    outcome = Table.ExpandRecordColumn(#"Expanded Column1", "Column1",
        Record.FieldNames(#"Expanded Column1"{0}[Column1]),
        Record.FieldNames(#"Expanded Column1"{0}[Column1])),
in
    outcome