cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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. 

 

So 

= (ItemID as text) => let

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

 

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:

DriveID\File

DriveID\Folder\File

DriveID\Folder\Folder\File

....

 

 

 

4 REPLIES 4
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) =>


let
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.id", "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
then
appendData
else @#"GetChildItems - loop"(driveID, NextItem, loopNum, appendData)

in
output

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

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors