Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

List.Generate from recursive loop

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's avatar
    Anonymous
    4 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.