Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sashaxp
Advocate I
Advocate I

Get 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:

  1. Get all files that resides on sharepoint site (not folder) through SharePoint Folder connector
  2. Filter out files that resides on Target folder
  3. 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.

 

 

 

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

7 REPLIES 7
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi @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()!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Hi @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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors