Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
s-roberts
Helper I
Helper I

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
lbendlin
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors