Dear All,
A few months ago i was struggling to find how to efficiently search files on a SharePoint. I was surprised there was no real info on this basic topic. Now that i finally found a (working) solution, I thought it would be good to share it.
The context : SharePoint.Files is great but extremely slow on very large drives. SharePoint.Contents is super fast but once you reach a folder, there is no way to search for files in subfolders (ex: if your files are organised by monthly subfolders).
The idea : Combine the best of the two world : use SharePoint.Contents to navigate to your desired folder and then use a Function to list all files from that folder (that will include files in subfolders).
The Function : Name it "ListerFichiersRecursifV3"
let
ListerFichiersRecursifV3 = (Dossier as table) as table =>
let
//Separe les fichiers des sous folders
Fichiers = Table.SelectRows(Dossier, each Record.HasFields(_, "Extension") and [Extension] <> null),
Dossiers = Table.SelectRows(Dossier, each Record.HasFields(_, "Content") and Type.Is(Value.Type([Content]), type table)),
SousFichiers = if Table.IsEmpty(Dossiers) then {} else List.Transform(Dossiers[Content], each @ListerFichiersRecursifV3(_)),
Resultat = Table.Combine(List.Combine({{Fichiers}, SousFichiers}))
in
Resultat
in
ListerFichiersRecursifV3
How to use it : In your main query, start with SharePoint.Contents, navigate the folders and after add a new step like "ListFiles = ListerFichiersRecursifV3(#"your previous step here")".
Results : You will see all files on the table that are in the folder & subfolders. Now you can filter on the Name column etc...
Why : This way of accessing files will speed up alot your "Evaluating..." refresh time hence less CPU usage and CO2 !!! It's really insane this isn't available directly in PowerM.
Enjoy 🙂
Gregory
