Show files and subfolders from SharePoint.Contents source.

[FM-Rad10]

How do I show all files within all the subfolders of the Automation folder? Ultimately I want to be able to show the parent folder name and modified date of each file. 

let

     Source = SharePoint.Contents("https://365.sharepoint.com/sites/C", [ApiVersion = 15]),

     T = Source{[Name="T"]}[Content],

     Control = T{[Name="Control"]}[Content],

     Automation = Control{[Name="Automation"]}[Content]

in

     #"Automation"

[FM-Rad10 · Accepted solution]

The way I've been able to get around this is this:

I had initally Expanded the Content field and then was unable to take it any further at the Expand Column option was removed. 

   

It turns out that there were 2 "Binary" files at this level (Excel docs) that were stopping any further expansion. 

After filtering them out I was able to Expand the Content.Content field and get to the level I needed and see all the file attributes for the docs I wanted. 

While this solution gets me what I need it's nowhere near perfect and defintely a bit hack'ey in its application. If I had needed to include those filtered docs then I still wouldn't have a solution as to how to get to the level below while still retaining the files from the level above. 

My Query now looks like this:

let

     Source = SharePoint.Contents("https://365.sharepoint.com/sites/C", [ApiVersion = 15]),

     T = Source{[Name="T"]}[Content],

     Control = T{[Name="Control"]}[Content],

     Automation = Control{[Name="Automation"]}[Content],

     #"Expanded Content" = Table.ExpandTableColumn(Automation, "Content", {"Content", "Name", "Extension", "Date modified"}, {"Content.Content", "Content.Name", "Content.Extension", "Content.Date modified"}),

     #"Filtered Rows" = Table.SelectRows(#"Expanded Content", each ([Content.Extension] = "")),

     #"Expanded Content.Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content.Content", {"Content", "Name", "Extension", "Date modified", "Folder Path"}, {"Content.Content.Content", "Content.Content.Name", "Content.Content.Extension", "Content.Content.Date modified", "Content.Content.Folder Path"})

in

    #"Expanded Content.Content"

[BA_Pete]

Hi @FM-Rad10 ,

I think you can just remove the [Content] part from the end of the Automation step.

Pete

[FM-Rad10]

Sadly it just shows the attributes from the Automation folder and nothing else. 

[BA_Pete]

Ah, ok. Try changing SharePoint.Contents to SharePoint.Files.

You should then be able to filter [Folder Path] on Text.Contains([Folder Path], "/Automation/".

Pete

[FM-Rad10]

well, after thinking that was the solution and even though it does work it's unusable as it's taken a report that refreshes in 3 mins to refreshing in 33 minutes. 

I'm now wondering if there's a way to extract the path from the step I'm on and using that within Folder.Files or something similar to show the required data. I've not come across a way yet. 

[FM-Rad10]

I had hoped to avoid Sharepoint.Files as it's so cumbersome but I think you're right. I can't seem to find a way of being able to apply a recursive search or jump from SP.Contents to SP.Files when I'm at the Automation folder.