Forum Discussion
Advocate IGet all files from onedrive folder in an efficient way
Hi folks,
I've tried to get files from onedrive/sharepoint folder following articles like that:
https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/
Common approach is to:
- Get all files that resides on sharepoint site (not folder) through SharePoint Folder connector
- Filter out files that resides on Target folder
- Use combine binary powerquery capability to combine and transform files in target folder
While step 1 runs more or less fast (even retrieving list of all the files located in sharepoint site), step 2 (filtering files that resides on target folder) executes several minutes which is not acceptable from ux point of view.
Is there any way to get list of files that resides in SPECIFIC folder and not sharepoint site, to radically improve response time?
Many thanks.
Yes. In the SOURCE line of the query, change SharePoint.Files("http://blahblah") to SharePoint.Contents("http://blahblah")
You will be at the root of the sharepoint site. You'll have to navigate through to the desired folder, but the end result will ignore all other folders when your query runs.
Change your source to this:
Source = SharePoint.Contents("https://sharepoint_site_root_name", [ApiVersion = 15]),See if that helps.
I'm not sure why it would take a long time to browse, unless the folder you are accessing has hundreds or thousands of files in that folder. It does have to load the full listing of the folder you ultimately get a file(s) from.
7 Replies
- edhans
Community Champion
Yes. In the SOURCE line of the query, change SharePoint.Files("http://blahblah") to SharePoint.Contents("http://blahblah")
You will be at the root of the sharepoint site. You'll have to navigate through to the desired folder, but the end result will ignore all other folders when your query runs.
sashaxpThanks for reply,
Tried that but still first and next time query processing duration is super long, like 10 minutes. Looks like navigating to Documents folder slows processing (row #2). When running query Queries & Connection tab showing it downloading ~50mb of metadata to show just what Documents folder has....
Here is code snippet i use to navigating through to the desired folder:Source = SharePoint.Contents("https://sharepoint_site_root_name"),
Documents = Source{[Name="Documents"]}[Content],
MyTargetFolder = Documents{[Name="MyTargetFolder"]}[Content],
- edhans
Change your source to this:
Source = SharePoint.Contents("https://sharepoint_site_root_name", [ApiVersion = 15]),See if that helps.
I'm not sure why it would take a long time to browse, unless the folder you are accessing has hundreds or thousands of files in that folder. It does have to load the full listing of the folder you ultimately get a file(s) from.