Forum Discussion

abbas_uddin's avatar
abbas_uddin
Helper I
6 years ago
Solved

Connecting to Sharepoint Specific Folder

Dear Experts,

 

i would like your inputs on how to connect to share point specific folder. The scenario is explained below:

 

i have connected to Get Data from Share Point Folder

-> Entered the root site

-> Established the connection

-> The window lands on the main page of Sharepoint folder and displays huge list of files

-> i have to go to the last column of path and try searching for path but sometimes it gives limit of 1000 values reached or takes too much time to load and appear

 

Is there any way i can load or can land on the sub folder path directly?

 

hope the above is cleared

 

Regards

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello abbas_uddin .

     

    had a quick check and it seems to be possible.

    - Connect to sharepoint

    - Select library

    - Add column to check folder name and filter only true

    - Expand Folder and Files

    here an example how it could work

     

    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

2 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello abbas_uddin .

     

    had a quick check and it seems to be possible.

    - Connect to sharepoint

    - Select library

    - Add column to check folder name and filter only true

    - Expand Folder and Files

    here an example how it could work

     

    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

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello
    are there any news on this topic? Did solve or help any reply your problem?
    If this is the case, please mark it as solution.

    Jimmy