Forum Discussion
Paginated List Query Error
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"
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.
4 Replies
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.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.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.
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.