Paginated List Query Error

[Mobleyjk]

Hello,

I'm working with the below query to pull in multiple tables of data from our API, but I'm receiving an error with one of my paramaters. I'd appreciate it if someone could point out where I've made a mistake.

Error:

Expression.Error: The field 'next' of the record wasn't found.
Details:
result=[List]

Query:

let
Source = List.Generate( ()=> [Result = Json.Document(Web.Contents("link.link")), Counter=0, next = Json.Document(Web.Contents("link.link"))[next]],
each [next] <> null,
each [
next = [Result][next],
Result = let
next = [Result][next],
offset = Text.BetweenDelimiters( next, "offset=", "&"),
limit = Text.AfterDelimiter(next, "limit="),
StartWithEmptyTable = Json.Document(Web.Contents("link.link", [Query=[offset=Text.From(offset), limit=Text.From(limit)]]))
in
StartWithEmptyTable,
Counter = [Counter] + 1
]
),
#"Converted to Table" = Table.FromRecords({Source})
in
#"Converted to Table"

[v-karpurapud]

Hi @Mobleyjk 

We have not yet heard back from you about whether our response addressed your query. If it did not, please share more details so we can assist you more effectively.

Thank You.

[v-karpurapud]

Hi @Mobleyjk 

We haven't received a response to our last message and wanted to follow up to see if you have found a solution. If you still need help, please share more details so we can assist you further.

Thank you.

[v-karpurapud]

Hi @Mobleyjk 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

 
Thank You.

[v-karpurapud · Accepted solution]

Hi @Mobleyjk 

Thank you for reaching out to the Microsoft Fabric Community Forum.

The error happens because the API response doesn't always include the 'next' field. When you get to the last page of the API pagination, the 'next' link can be null or missing. The current M code tries to access the 'next' field without checking if it exists, which leads to this error: Expression.Error: The field 'next' of the record wasn't found.

Please try the below M Code :

let

    InitialUrl = "link.link",



    GetPage = (url as text) as record =>

        let

            Response = Json.Document(Web.Contents(url)),

            NextLink = if Record.HasFields(Response, "next") then Response[next] else null,

            ResultData = Response

        in

            [Result=ResultData, Next=NextLink],



    Pages = List.Generate(

        ()=> GetPage(InitialUrl),

        each [Next] <> null,

        each GetPage([Next]),

        each [Result]

    ),



    #"Converted to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

    #"Converted to Table"

I hope this information is helpful. If you have any further questions, please let us know. If this does not helps please share more details so we can assist you further.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.