Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
....
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.