Sharepoint Data Connection ("Operation Timed Out Error")

[Rudresh8690]

Hello Everyone,

I've connected my data from a SharePoint Folder into Power BI Desktop.
In the SharePoint folder, I store monthly Excel files, which are then combined in Power Query.

Recently, when I added the latest file, I encountered an error:

"Operation Timeout Error"

After investigating, I found that the new file had more than 5000 rows. When I reduced it to 5000 rows or less, everything worked fine. However, increasing the row count again caused the timeout error to return.

This made me wonder:

• Is there a row limit when using the SharePoint folder connector in Power BI?

• Or is there a performance limitation causing this timeout for large files?

I’d appreciate any insights or best practices for handling larger Excel files in SharePoint with Power BI.

Thanks in advance!

@lbendlin @d_gosbell @rohit1991 @SolomonovAnton @uzuntasgokberk @Greg_Deckler 

[Akash_Varuna]

@Rudresh8690 It is likely due to performance limitations with large files in SharePoint. Power BI struggles with sharepointconnector
Please Check this thread for detailed information which explains the situtaion
https://www.reddit.com/r/PowerBI/comments/1fnxcsl/sharepoint_connector_limitations/

[Rudresh8690]

Hello Akash, 
I am using the SharePoint Folder Connection, so does Power BI treat Excel Files as Lists?
As much as I have researched, there is a limit of 5k rows in lists, even that's not properly mentioned everywhere so I have doubt in that.

[v-saisrao-msft]

Hi @Rudresh8690,

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

Thank you @Akash_Varuna, for your insights.

Power BI does not treat Excel files from a SharePoint Folder as SharePoint Lists, so the well-known 5000-item threshold from SharePoint Lists does not apply in this case. The timeout error you’re encountering is more likely related to performance limitations rather than a strict row limit. When Power BI connects to a SharePoint Folder, it relies on SharePoint’s REST APIs to retrieve and combine files, and this process can become sluggish especially with larger files or when multiple transformations are applied in Power Query.  

Try optimizing your Excel files by removing unnecessary formatting, formulas, or columns, which can significantly reduce load time. If you're using the default Combine Files function in Power Query, consider applying row or column filters as early as possible in the query steps to minimize the volume of data being processed. 

If you're working with just a few large files, another effective approach is to use the Excel Web connector by pasting the direct file URL from SharePoint. This bypasses the SharePoint Folder API overhead and is often faster. If your data volume continues to grow, you might want to explore using Power BI Dataflows, which run in the cloud and can handle larger datasets more efficiently than Power BI Desktop.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[Rudresh8690]

Hello

I have tried optimising the Excel Files, it actually has 25k rows, I reduced it to 15k, pasted all the data as values kn excel, but as soon as the row number increases more than 5k, I get this error.

Also it is working fine in my personal laptop where I user Power BI Desktop.

But I'm currently using my client's laptop and this organisation use Power BI Report Server, I am having issue here only.

[v-saisrao-msft]

Hi @Rudresh8690,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Rudresh8690]

Hello, 

No it didn't solve my problem yet, I had to use local connection as I keep getting the error.

And even If I split excel files, then it will take so much time and efforts and is not the practice our client would want to comduct.

But I'm still now satisfied as if there's an error for 5k rows in SharePoint Folder Connection, it should have written somewhere.

[v-saisrao-msft · Accepted solution]

Hi @Rudresh8690,

Since the issue still persists despite the troubleshooting steps, I recommend raising a support ticket so that one of the engineers can investigate it further and provide a solution to the issue you are facing.

You can raise a support ticket using the link below:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[v-saisrao-msft]

Hi @Rudresh8690,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft]

Hi @Rudresh8690,

Ensure you're using the correct version of Power BI Desktop that matches the Report Server version, increase timeout settings in the Report Server config files, and consider using the Excel Web connector by directly linking to the file instead of using the SharePoint Folder connector. This can improve performance and bypass potential connector limitations.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.