SharePoint Online Folder Parameters

[Anonymous]

Hi,

My company is moving to SP onine, and I'm in the process of migrating a lot of my source data and chaging data source paths for my reports... I have a document library on my site that contains a dozen + different folders within the one library.

I previously had a separate query for each of those folders; however, now when I try to connect to the SP online site, it only reads the library - not the folders themselves. When I try to combine and transform, It pulls all of the documents within the library... there is no option to limit by folder name. I'm trying to combine excel files within a given folder, but the list is too long to see most of the documents. I'm sure there's a way to do this with parameters, I just don't know how yet.

I did try to connect to one Table and the helper Query/sample file for the combine is pointing to the full folder path, but I can't (or don't know how) to change that in the parameter of the main query!

Query1

Source

= SharePoint.Files("https://ABC123COMPANY.sharepoint.com/sites/Metrics", [ApiVersion = 15])

invoke custom function (combine files)

Sample File Parameter

= Source{[Name="Drilldown 010419.xlsx",#"Folder Path"="https://ABC123COMPANY.sharepoint.com/sites/Metrics/AllReport/Drilldown/"]

The helper query is totally fine, but back on the main query, it breaksdown on the expanded table columns step because the key doesn't match - because it's pulling everything, instead of files from the one folder I want.

Please help!

Thank you,

Kim

[Jimmy801]

hello @Anonymous ,

on the normal sharepoint it was possible to navigate to the folder as described in following example. However I don't have access to a online sharepoint and therefore can't test it there. Give it a try and let us know

let
    Source = SharePoint.Tables("YourSharepoint", [ApiVersion = 15]),
    GotoLibrary = Source {[Id="IDofYourLibrary"]}[Items],
    RenamedColumns = Table.RenameColumns(GotoLibrary,{{"ID", "ID.1"}}),
    FilterForURL = Table.AddColumn
    (
        RenamedColumns,
        "ContainsFilterCriteria",
        each if Text.Contains(_[Folder][ServerRelativeUrl], "YourSearchCriteriaForFolder") = true then true else false
    ),
    FilteredRows1 = Table.SelectRows
    (
        FilterForURL, 
        each ([ContainsFilterCriteria] = true)
    ),
    RemovedOtherColumns = Table.SelectColumns
    (
        FilteredRows1,
        {"Folder"}
        ),
    ExpandedFolder = Table.ExpandRecordColumn
    (
        RemovedOtherColumns, 
        "Folder", 
        {"ItemCount", "Name", "ServerRelativeUrl", "WelcomePage", "Files", "ListItemAllFields", "ParentFolder", "Properties", "Folders"}, {"ItemCount", "Name", "ServerRelativeUrl", "WelcomePage", "Files", "ListItemAllFields", "ParentFolder", "Properties", "Folders"}
    ),
    ExpandedFiles = Table.ExpandTableColumn
    (
        ExpandedFolder, 
        "Files", 
        {"CheckInComment", "CheckOutType", "ContentTag", "CustomizedPageStatus", "ETag", "Exists", "Length", "Level", "MajorVersion", "MinorVersion", "Name", "ServerRelativeUrl", "TimeCreated", "TimeLastModified", "Title", "UIVersion", "UIVersionLabel", "Author", "CheckedOutByUser", "ListItemAllFields", "LockedByUser", "ModifiedBy", "Versions"}, {"CheckInComment", "CheckOutType", "ContentTag", "CustomizedPageStatus", "ETag", "Exists", "Length", "Level", "MajorVersion", "MinorVersion", "Name.1", "ServerRelativeUrl.1", "TimeCreated", "TimeLastModified", "Title", "UIVersion", "UIVersionLabel", "Author", "CheckedOutByUser", "ListItemAllFields.1", "LockedByUser", "ModifiedBy", "Versions"}
    )
in
    ExpandedFiles

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous · Accepted solution]

When I connect to my Sharepoint online, I receive a field called Folder Path. I am able to filter on this path. Are you not getting the same field?

[Anonymous]

Yes, I am. Thanks.

[dax]

Hi Kimberly_MT, 

I think you need to make sure each Excel in sharepoint folder are the same(column name and type), if they are not same, the next steps will prompt error(can't find column or key don't match). You could check this in your sample.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This was not the issue - my problem is that I have one doc library with several folders - each folder contains multiple files - the files all share the same schema. I was just trying to set a parameter to narrow down by folder.