Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Looping API call to fetch children Teams drive items

I'd need to loop thru multiple calls to fetch nested files and folder items from a Teams document library. 


I cannot determine from the first root call how many levels of folders I will need to go to fetch all files.

The function should drill down until there are no more child records (child count field returns a 0 or null).


As a starting point, I have a table with the root folder (driveID) and that fetchs the drive items ids.

Then this function will fetch the child items for that table. 



= (ItemID as text) => let

Source = Json.Document(Web.Contents("https://XXX=/beta/drives/" & ItemID & "/children?$format=application/json&clientid=XX&secret=XX&tenantid=XX"))


This returns each id for the next level, and a childCount. 

If the childCount is > 1, then I'd need to run this fetch again.. and again until there are no longer any child items.


Is there a method to do this in Power Query. 

I did see pagnation solutions, but this is a call that will need to use the id's returned in the resultset as part of the new call for the next level.... 


The results should be:








Super User II
Super User II

yes, you can use recursive functions but you need to store your harvested data somewhere.  Most of the time you use lists for that, starting with an empty one,  adding the results in the folder and then recursively calling  yourself for all folder children


(ItemID as text, Result as list) => let ...


and then call it like


Full List = function(rootItemID,{})


This is my function but I get error: query. Expression.Error: We cannot convert a value of type List to type Table.




(optional driveID as text, optional itemID as text, optional childcount as number, optional loop as number, optional data as list) =>

Source = Json.Document(Web.Contents("https:XXendpoint=/beta/drives/" & driveID & "/items/" & itemID & "/children?$format=application/json&clientid=XX&secret=XX&tenantid=XX")),

Expand = Table.ExpandListColumn(Source, "DriveChildItems"),
Expand1 = Table.ExpandRecordColumn(Expand, "DriveChildItems", {"createdDateTime", "folder", "id", "lastModifiedDateTime"}, {"DriveChildItems.createdDateTime", "DriveChildItems.folder", "", "DriveChildItems.lastModifiedDateTime"}),
Expand2 = Table.ExpandRecordColumn(Expand1, "DriveChildItems.folder", {"childCount"}, {"childCount"}),
NextItem = Expand1[id],
Nextchildcount = Expand2[childCount],
currentData = Expand,
appendData = List.Combine({data, currentData}),

loopNum = loop + 1,
output =
if NextItem is null or Nextchildcount is null or Nextchildcount = 0 or loopNum > 3
else @#"GetChildItems - loop"(driveID, NextItem, loopNum, appendData)



Did you figure out which code line causes that?  May have to throw in a conversion before and after.


No. I'm unable to determine what causes the error.  I have used table functions to expand the list to fetch the next ids for parameters for the next loop.  But the output should be a combined list of lists.


Helpful resources

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!


Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors