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"
Hi FM-Rad10 ,
I think you can just remove the [Content] part from the end of the Automation step.
Pete
Sadly it just shows the attributes from the Automation folder and nothing else.
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.
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.