Forum Discussion
PowerBI Loop/Recursive Function
Can anyone see what i am doing wrong?You are using a recursive function 🙂 . They are cute but mostly useless especially when you have lots of data that you have to lug around in the bowels of the recursion. It is much better to use List.Generate if possible and List.Accumulate if necessary, with a healthy helping from your browser cache. Or, if you want to be really clever and the scenario supports it (which yours unfortunately doesn't) you can spread out the API call into multiple queries for maximum parallelism and speed.
Here's an article that may be of help Handling paging for Power Query connectors - Power Query | Microsoft Learn
Thanks, took your approach below, but am still getting an error saying that it can't find the nextToken on the record eventhough i've confirmed it is there (at least for the first few pages). Any thoughts?
GetPages = (url as text) as list =>
let
GetPageData = (url as text) =>
let
Source = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & token, Accept="application/json"]])),
data = try Source[data] otherwise null,
nextToken = try Source[nextToken] otherwise null
in
[Data = data, NextToken = nextToken],
initialData = GetPageData(url),
accumulatedData = List.Generate(
() => initialData,
each [NextToken] <> null,
each GetPageData(url & "&nextToken=" & Text.From([NextToken])),
each [Data]
),
result = List.Combine(accumulatedData)
in
result,