Power BI Sharepoint Online List Connector 2.0 returning 2000 rows

[CoreyM3]

Hello I have an issue where a powerquery querying a sharepoint online list is now pulling exactly 2000 rows despite pulling all 6000+ rows before.

I am able to replicate the issue in a fresh power bi file pulling in the list. The only thing I can think of is something in the configuration of the list got changed but cannot fathom what it would be.

[Ritaf1983 · Accepted solution]

Hו @CoreyM3 

This is a known issue with the SharePoint Online List Connector 2.0. It occurs because the RenderListDataAsStream API used by this version often stops at the first batch limit (2,000 rows) and fails to execute proper pagination.

Direct Solutions:

Switch to Implementation 1.0: This is the most reliable fix. In the Power Query M code, change the parameter Implementation="2.0" to Implementation="1.0". While version 1.0 is slower, it consistently handles datasets exceeding 5,000 rows.

Adjust ViewMode: Change ViewMode="All" to ViewMode="Default". The "All" mode sometimes forces the query through a specific SharePoint view that has row limits applied.

SharePoint Indexed Columns: If you apply filters within Power Query, ensure the filtered columns are set as Indexed Columns in the SharePoint list settings. Without indexes, the API frequently hits a threshold at 2,000 or 5,000 rows and fails to retrieve the rest.

Professional Recommendation:
For lists with 6,000+ rows, use Implementation 1.0 if version 2.0 fails to paginate correctly.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[v-priyankata]

Hi @CoreyM3 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@parry2k @Ritaf1983 @collinq  Thanks for the inputs

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @CoreyM3 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[collinq · Accepted solution]

Hi @CoreyM3 ,

SharePoint has a row limit of 2000 rows if query folding is broken and/or it defaults to a default size (2000) when it runs into issues - like non-indexed columns.  Has anything been added to your views lately?

I would check on SharePoint to determine if there are changes in columns, lookup tables or a column type that was modified in some way.

[Ritaf1983 · Accepted solution]

Hו @CoreyM3 

This is a known issue with the SharePoint Online List Connector 2.0. It occurs because the RenderListDataAsStream API used by this version often stops at the first batch limit (2,000 rows) and fails to execute proper pagination.

Direct Solutions:

Switch to Implementation 1.0: This is the most reliable fix. In the Power Query M code, change the parameter Implementation="2.0" to Implementation="1.0". While version 1.0 is slower, it consistently handles datasets exceeding 5,000 rows.

Adjust ViewMode: Change ViewMode="All" to ViewMode="Default". The "All" mode sometimes forces the query through a specific SharePoint view that has row limits applied.

SharePoint Indexed Columns: If you apply filters within Power Query, ensure the filtered columns are set as Indexed Columns in the SharePoint list settings. Without indexes, the API frequently hits a threshold at 2,000 or 5,000 rows and fails to retrieve the rest.

Professional Recommendation:
For lists with 6,000+ rows, use Implementation 1.0 if version 2.0 fails to paginate correctly.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[parry2k]

@CoreyM3 is this 2000 you are seeing in Power Query or when you apply the changes, you only see 2000 rows after table is loaded.

[CoreyM3]

2000 is a limit I am seeing in the editor and once loaded in.