Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.