List.Generate from recursive loop

[Anonymous]

Hello PBI friends,

I would like to get help with the following. I'm creating a loop for the first time. It's working for the first pages, but at some time it's causing a stack overflow. Now I know that a List.Generate could help with this but I don't know how to transform my code to work with List.Generate. Can anybody help me out? This is the code:

let
AuthKey = "Basic XXXX",
url = URL&"/api/oauth/v1/token",
body = "{ ""grant_type"": ""password"", ""username"": ""XXXX"", ""password"": ""XXXX""}",

tokenResponse = Json.Document(Web.Contents(url,[

Headers = [#"Authorization"=AuthKey ,
#"Content-Type"="application/json"],
Content = Text.ToBinary(body)]
)),
access_token = tokenResponse[access_token],
token = "Bearer " & tokenResponse[access_token],

baseuri = URL&"/api/rest/v1/products?pagination_type=search_after&search_after=&limit=100",
headers =[Headers = [#"Authorization"=token, #"Accept"="application/json"]],
initReq = Json.Document(Web.Contents(baseuri,headers)),
initData = List.Buffer(initReq[_embedded][items]),
gather = (data as list, baseuri) =>

let
newOffset = Json.Document(Web.Contents(baseuri, headers))[_links][next][href],
newUri = newOffset,
newReq = Json.Document(Web.Contents(newUri, headers)),
newdata = List.Buffer([_embedded][items]),
data = List.Combine({data, newdata}),
check = if newReq[_links][next] = null then data else @gather(data, newUri)
in check,

outputList = if initReq[_links][next] = null then initData else gather(initData, baseuri),
expand = Table.FromRecords(outputList)
in
expand

Thanks in advance.

[Anonymous · Accepted solution]

Hi @Anonymous ,

There's a parctical example in the following blog, hope it can solve your problem:

Recursive Functions in Power BI / Power Query — The Power User

The recursive function

fxTranslate = (x as table, n as number, ColName as text ) as table =>

       let

           Replace = Table.ReplaceValue(x, Old{n}, New{n}, Replacer.ReplaceText,{ColName}),
Checking = if n = List.Count(Old)-1 then Replace else @fxTranslate(Replace, n+1, ColName )
in
Checking

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.