If you work with SharePoint lists, you’ve probably bumped into the list view threshold at one point or another.
The gist is: SharePoint can only display 5000 items at a time in a list view.
- In classic experience, this completely prevents the view from loading anything at all when over the threshold.
- In modern, Microsoft made it less of a big deal in that the view still seems to function – but will not return more than 5000 items, and may not sort and filter properly over that threshold.
What’s the easiest way to deal with the list view threshold?
In my opinion, the easiest way to handle this in cases where you expect that the return might be over 5,000 at some point in the future, is to put a dynamic date filter on the view. Something along the lines of “created date is in the last two years” is my go-to.
To do this, edit the list view:
Under the “Filter” section, you can set a filter on the Created (this is created date) field to greater than or equal to “[Today]-730”.
- 730 here is the number of days in a year x2 (the date filter is measured in days).
- [Today] is a kind of dynamic filter context, and will always represent the current date.
Make sure the field you’re filtering on is indexed in your list settings under the “columns” section while you’re at it, as you could have issues adding indexes or filtering un-indexed fields when you get over the 5k threshold:
What if I’m already over 5k?
Modern SharePoint actually handles this way better than classic – give the above a shot and see if it’ll let you proceed with the process above. I’ve tested it recently with just over 5,000 and not had issue, but I DO recall this being problematic in the past, so your mileage may vary. If you’re just a little bit over, you can always delete a few items, set up your views and settings, then restore those items from the recycle bin.
How does Power BI handle large lists?
Since Power BI does not interact with the list view, it doesn’t care so much about the view threshold – BUT this strongly depends on which connector you’re using.
At present, the “v2.0” SharePoint list connector has a return threshold of 5,000 as well, meaning if you expect more than 5,000 items, you should use the 1.0 connector (under Implementation below). They output data in a very different structure, so if you think at any point you might get over 5,000 rows, it’s better to start with the 1.0 connector – it’s hard to swap out later, without having to redo a lot of work.
This makes Power BI a kind of clever workaround for list view difficulties – you can essentially recreate the list view using the Table visual in Power BI, and create conditional hyperlinks on the item titles to have them open the form in SharePoint just like a list view would. It doesn’t take an excessive amount of time to configure in a user-friendly way (/cough unlike POWER APPS /cough).
I made a video on the process that goes through all the steps to get an interactive experience up and running in Power BI with a SharePoint List source, for those that are unfamiliar with one side of the equation or the other.
You might still experience performance issues with excessively large lists, particularly if you’re using a lot of person or lookup columns. I like to do the primary ETL for these in a Power BI Dataflow, because it front-loads a lot of the processing work. There’s another clever technique involving using the OData connector here (this article is for Excel, but many of the same solutions apply to lists).