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!

SharePoint File Retrieval Limit

Hello,

 

I posted this in the Desktop section of the forums, but I feel that this is a bug and it is something that is hindering a project that my company is working on. 

I believe that there is a limit to the number of files that Power BI can retrieve from SharePoint. When retrieving from SharePoint, Power BI retrieves from the root folder and extracts all files from the root folder and its sub-folders, without hierarchy. My company has many subfolders in our SharePoint directory, with many files in it. When querying, I notice that many files are missing when the files are listed in the query editor. I feel that the missing files are due to the large number of files that we have in this particular library. Is there a known file limit for SharePoint?

 

Thanks,

Status: Needs Info
Comments
1tcook
Frequent Visitor

Not sure. Perhaps the query method bypasses the threshold. Either way it explains the 5,000 file limit that is mentioned.

 

Try creting a list view of the most recent 5,000 items.

 

Here is the MS page regarding it: https://support.microsoft.com/en-ca/help/2759051/the-number-of-items-in-this-list-exceeds-the-list-v...

edhans
Super User

FYI - there is a better way. When you do your initial connection Power Query will generate a SOURCE line that is SharePoint.Files("HTTPS://blahblahblah....")

 

Change it in the formula bar to SharePoint.Contents("HTTPS://blahblahblah)

 

Now you are only looking at folders. Just navigate to the folder of choice.

Two things to be mindful of:

  1. Unlike SharePoint.Files, SharePoint.Contents can only look at one folder of files a time if you do any binary combining. So if data is stored by year and month for example using folders and subfolders, SharePoint.Contents won't work.
  2. If you have more than 5,000 files in a single folder. SharePoint.Contents won't show you more than 5,000 in that one folder.

 

But if you store all of your data files for a particular report in a single folder, this will let you rapidly navigate to that folder and get those files no matter how many files are in your SharePoint site.

Anonymous
Not applicable

thanks @edhans , you saved my day

SYM1986
Helper I

Hi @edhans , so excellent solution ,that's exactly what i looking for.

Thanks a lot