Forum Discussion
ESE_fh
3 years agoFrequent Visitor
Get all combined subDirectories with Folder.Contents() from multiple servers (mainDirectories),
Dear Pro-Users, most likely an easy question, What is a performant PQ best practice to query Folder.Contents (directories only) from different servers/directories? starting from a table with all d...
- 3 years ago
No, you need to bring out the big guns. AD extracts, Purview, Scanner API. Low code/no code tools tend to be overwhelmed when confronted with massive amounts of data.
ESE_fh
3 years agoFrequent Visitor
Hopefully pointing to a working solution: to be further generalized by recursive call, i.e. flexible folder-levels.
Idea: Each User-Folder-Select returns parent/child folders, which is like browsing the windows explorer (standard)
(Pretty sure, there is a conformant & better standard off-the-shelf solution, but ignored or over looked here)
// fct named GetSubDirs (apology if too trivial, mimics only one step of explorer browsing)
= (MainDir as text) as table =>
let
// GetSubDirs queries (for each row in a table) MainDir only the next level SubDirs
Source = Folder.Contents(MainDir),
SubDir = Table.SelectColumns(Table.SelectRows(Source, each ([Attributes][Directory] = true)),{"Folder Path", "Name"})
in
SubDir
calls custom-fct (probably be better & faster done as if nested)
let
// Sub2Dirs (table[column] with Sub2Dirs) gets for each row get all next level Sub3Dirs
Sub2Dir = Table.TransformColumnTypes(Table.SelectColumns(Excel.CurrentWorkbook(){[Name = "Sub2Dirs"]}[Content], {"Sub2Folder"}),{{"Sub2Folder", type text}}),
CustFct = Table.TransformColumnTypes(Table.ExpandTableColumn(Table.AddColumn(Sub2Dir, "SubDir", each GetSubDirs([Sub2Folder]), type text), "SubDir", {"Name"}, {"Sub3Dir"}),{{"Sub3Dir", type text}}),
Result = Table.SelectColumns(Table.SelectRows(Table.AddColumn(CustFct, "Sub3Folder", each Text.Combine({[Sub2Folder], [Sub3Dir]}, ""), type text), each ([Sub3Folder] <> "")), "Sub3Folder")
in
Result- lbendlin3 years agoSuper User
This can be "simplified" by using recursive functions. But will it be faster? Not likely.