Forum Discussion
Show files and subfolders from SharePoint.Contents source.
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:
letSource = 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"
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
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.