Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SharePoint Online Folder Parameters

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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?

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      Yes, I am. Thanks.
      • dax's avatar
        dax
        Community Support
        6 years ago

        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.

         

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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