SharePoint File Retrieval Limit

[vega]

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,

[1tcook]

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]

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]

thanks @edhans , you saved my day

[SYM1986]

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

Thanks a lot