Forum Discussion
5000 threshold sharepoint connection - PBI
i would like to overcome a (500): Internal Server Error related to 5000 threshold from sharepoint.
I have a PBI connected to sharepoint list using "sharepoint online list" connector. After the list hit the 5000 items, I cannot refresh or create a new connection.
How can I resolve this issue?
Hi jps_HHH
To what the others are recommending, if there is a date column that is used for your data, you could then apply incremental refresh to this particular table, which potentially then would only refresh the new data and not go past the 5000 limit because you're doing it on a daily basis. So each day we'll have our own separate query.
10 Replies
Hello jps_HHH
You may want to refer to this post
In summary from this post, the workaround would be either create an Indexed Column in SharePoint, then filter on that column in Power BI queries, which lets SharePoint use the index and avoid the threshold,
Or, apply Filters at Source, which forces SharePoint to only return rows within the indexed range, like
Filtered = Table.SelectRows(List, each [Created] >= #date(2024,1,1))Hope this helps:)
I did not understand the indexed column concept.
However, I've already created a indexec column in the sharepoint. But aftwerads I tried to reconnect with PBI and the same errror occurs.
I dont know what should I do after creating indexed columns.
BTW, I tried to use 1.0 sharepoint online list and it worksIn my example M,
Filtered = Table.SelectRows(List, each [Created] >= #date(2024,1,1))
[Created] is the indexed column. By filtering before any other transformation, SharePoint only returns the rows within that range.
In addition to what MasonMA mentions, you may consider using the OData connector. I have had better luck with this connector for large lists. Please see the following for others discussing this as well: https://www.reddit.com/r/PowerBI/comments/mra2qj/problem_with_sharepoint_list_connection/
I tried to Odata connector and it says that we couldn't authenticate with the creadiatels provieded (I use my creaditioans in "organiztional account" pane )
Hi jps_HHH
To what the others are recommending, if there is a date column that is used for your data, you could then apply incremental refresh to this particular table, which potentially then would only refresh the new data and not go past the 5000 limit because you're doing it on a daily basis. So each day we'll have our own separate query.
Hi jps_HHH,
Thank you for reaching out to the Microsoft Fabric Forum Community.
Just following up to see if the Response provided by community members were helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna Kumar
Hi jps_HHH,
Just following up to see if the Response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.
Best regards,
Prasanna Kumar
Hi jps_HHH,
Just following up to see if the Response provided by community members were helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna Kumar
