PowerQuery List.Generate: Order of Argument Evaluation?

Question

Regarding PowerQuery's List.Generate function.

Could someone please help me understand why the first version of this List.Generate call returns 2 elements (desired result), while the second returns only 1 element (only the initial value - an empty list)? The only difference in the two calls is found on line 3.

Working query

Source = 
        List.Generate(
            ()=>[Page = 1, NextPage = "next", Response = [next = "next", results = {}]],
            each [Page] <= MaxPages and NextPage <> null,
            each[
                Page = [Page] + 1,
                Response = Json.Document(Web.Contents("https://platform.myapi.com/public_api?",
                                                        [
                                                           Query=
                                                            [
                                                                page=Text.From([Page]),
                                                                page_size=Text.From(1000)
                                                            ],
                                                           Headers=[#"x-api-key"=#"ApiKey"],
                                                           RelativePath=relativePath
                                                        ]
                                                       )
                                          ),
                NextPage = [Response][next]
            ],
            each [Response][results]
        ), ...

good result set2 Correct. 2 lists - the initial, empty list, and the results of the API call

Query I think should work, but doesn't

        List.Generate(
            ()=>[Page = 1, NextPage = "next", Response = [next = "next", results = {}]],
            each [Page] <= MaxPages and [Response][next] <> null, // only change
            each[
                Page = [Page] + 1,
                Response = Json.Document(Web.Contents("https://platform.myapi.com/public_api?",
                                                        [
                                                           Query=
                                                            [
                                                                page=Text.From([Page]),
                                                                page_size=Text.From(1000)
                                                            ],
                                                           Headers=[#"x-api-key"=#"ApiKey"],
                                                           RelativePath=relativePath
                                                        ]
                                                       )
                                          ),
                NextPage = [Response][next]
            ],
            each [Response][results]
        ),...

Incorrect. Only the initial empty list

For clarity, here is sample output from the API. It’s a paginated API with 1,000 records per page. So, since there are only 697 records returned, there is no further page to pull, and the next element is returned as null. There are 697 elements in the results array.

Murray Foxcroft Murray Foxcroft · Accepted Answer · 2020-09-24T06:04:03

In you data, as you state, Response[next] is null (since you only have 687 items).

So the condition:

each [Page] <= MaxPages and [Response][next] <> null,

Is never met on line 3 of query 2 and no further processing happens.

In the first query, NextPage = "next", so this works.

PowerQuery List.Generate: Order of Argument Evaluation?

Working query

Query I think should work, but doesn't

1 Answers