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