5000 threshold sharepoint connection - PBI

[jps_HHH]

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?

[v-pgoloju]

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

[v-pgoloju]

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

[v-pgoloju]

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

[GilbertQ · Accepted solution]

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.

[blopez11]

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/

[jps_HHH]

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 )

[MasonMA]

Hello @jps_HHH 

You may want to refer to this post

https://community.fabric.microsoft.com/t5/Power-Query/SharePoint-list-view-threshold-error-when-impo....

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

[jps_HHH]

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 works

[MasonMA]

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

[jps_HHH]

But I cannnot connect the sahrepoint list to PBI. the error happens in the second step of the powerquerry, i dont have chance to filter the data that I want to retrive from sharepoint