Use List.Generate while looping values from another table

[PatrickM]

The m code below attempts to loop a single-column table (Project_IDs) and pass its values into a List.Generator function, and add the output(project task information) to a 'comboList' to accumulate a growing list of List.Generator outputs. 

Code explanation:

Each loop passes a project id to the GetDataFromApi function (using List.Generator for pagination) to fetch a project's task information until the api returns a null value.  

When List.Generator is done, its output list is added to 'comboList', which should get bigger and bigger as the loop runs and the loop eventually passes all project id's to List.Generator.  (This List.Generator function works when I pass it only one project id)

After each List.Generator output is added to comboList, an if statement checks if the loopCount has exceeded the number of project id's, if not (and there are still project id's to pass to List.Generator) the function is recalled using the @ character with 1 added to the loopCount. 

Notes: 

This is a pretty simple thing to do with python or JS etc.  Is using m code the best way of doing this?  Or is creating a new table for each project id preferable and combining them into one table as a final step?  This seems like it should work but it currently returns an empty list. Any alternative methods of accomplishing this are greatly appreciated. Thanks

(loopCount as number, baseList as list) =>
let
  //Reference Project_IDs table to define the total number of Project IDs to pass to the List.Generate function
  //Keep looping this function until this number is exceeded
  totalLoops = Table.RowCount(Project_IDs) as number,

  //Use List.Generate to create a list of lists from external API, pass in project id value from ProjectIDS table
  //The api only returns a limited number of tasks; to fetch them all, multiple calls are made by List.Generate using the GetDataFromApi function
  tempListOfTasks = List.Generate(  ()=>  
    //INITIAL VALUE   
    [Result= try GetDataFromApi(1,Project_IDs{loopCount}) otherwise null, Page = 1,IdIndex = 0],
    //CONDITION CHECK
    each [Result] <> null and [Page] < 6,
    //IF CONDITION MET BELOW IS THE VALUE TO CREATE NEXT
    each [Result = try GetDataFromApi([Page]+1,Project_IDs{loopCount}) otherwise null, Page = [Page]+1],
    each [Result]
    ),

    //Add List.Generate output to baseList, each projectIDs itteration should add new lists to comboList
    comboList = List.Combine({{baseList}, {tempListOfTasks}}), 

    output = 
    //IF LoopCount exceeds number of projectIds(totalLoops) return comboList
    if loopCount > totalLoops
    then comboList //Number.ToText(loopCount) &  ", " & Number.ToText(totalLoops) //tempTable
    //IF LoopCount has yet to exceed number of projectIds(totalLoops) rerun function while passing in loopcount+1 and combolist
    //This should keep looping until all Project Ids have been passed to the List.Generate function
    else  @ProjectTaskLoopInsideProjectIDLoop(loopCount+1, comboList)
in 
    output

[SaiKrishnapola]

Hello @PatrickM , were you able to resolve the issue?

[PatrickM]

Yes, I used @AlexisOlson solution seen below. It seems like splitting functions up and using List.Generate function to loop a list and call a separate function for each item in the list, is the prefered way. 

[v-kkf-msft]

Hi @PatrickM ,

Since it is easy to implement in Python, maybe it is faster to import data using Python scripts. For more information, please refer to: https://docs.microsoft.com/power-bi/connect-data/desktop-python-in-query-editor 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

I'd recommend avoiding recursion when you don't need it.

If you simplify your function to this (let's call it GetPages):

(ID as number) =>
  List.Generate(
    () => [ Page = 1, Result = try GetDataFromApi(1, ID) otherwise null ], 
    each [Result] <> null and [Page] < 6,
    each [ Page = [Page] + 1, Result = try GetDataFromApi([Page] + 1, ID) otherwise null ], 
    each [Result]
  )

Then you can call the function for each item of the Project_IDs list and then combine them rather than recursively building a list.

let
    Source = Table.FromColumns({Project_IDs}, {"ID"}),
    #"Added Custom" = Table.AddColumn(Source, "TaskList", each GetPages([ID])),
    #"Expanded TaskList" = Table.ExpandListColumn(#"Added Custom", "TaskList")[TaskList]
in
    #"Expanded TaskList"