Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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.
Solved! Go to Solution.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingChange 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes. 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks 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],
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans,
I've changed source to include ApiVersion and now everything works super fast.
Thanks a lot!
Cool. I don't know exactly what the ApiVersion does, but I do know it limits how many files it pulls (low thousands). If using SharePoint.Contents() that shouldn't be an issue since an individual folder should not have thousands of files in general. So it stops PowerBI from pulling endlessly to get all of the file and folder names. It has a decent effect on SharePoint.Files, but apparently a huge effect on SharePoint.Conents()!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @sashaxp
As far as I understand the users refresh data from PBI Desktop?
Depending on the license available, do you want to consider creating a dataflow or separate PBI dataset to update the underlying data every time any file in the Sharepoint folder is updated? This can be done using MS Flow and PBI REST API bridge.
The user dataset/reports can be hooked into this central data. Refreshing from dataflow or another PBI is close to instant (depending on the size of the actual underlying dataset).
Kind regards,
JB
Hi @Anonymous;
Thanks for suggestion, but at the moment i am trying to make it simple and also slow processing solved as per @edhans suggestions on using SharePoint.Contents instead of SharePoint.Files api calls.