PowerBI and Sharepoint Online List - Web.Contents failed to get contents

[RalZRR]

Hi guys,

I've encountered an issue with PowerBI dataset and Sharepoint Online list.

Scenario:

1. There is a Sharepoint list used by employees to enter hours for projects

2. Users use PowerApps to enter their hours

3. PowerBI is used to display reports of the hours by projects etc.

The issue, I think, is narrowed down to the following:

The SP Online list in use reached 5k items yesterday and since that the dataset has been unable to refresh with the following error:

Web.Contents failed to get contents from 'https://company.sharepoint.com/sites/sitename/_api/Web/Lists(guid'xxx')/RenderListDataAsStream' (500): Internal Server Error

The dataset uses Shareponit Online List 2.0 data connection.


Are there any workarounds for this issue or an alternative has to be found to store the data?

Thank you!

[justinbouchard]

Ran into this today. Tried v 1.0 connector and it seems to work. wasn't able to authenticate with the OData route. Will dive into it more but it was a quick fix that seemed to work.

[RalZRR · Accepted solution]

@Syndicate_Admin 
Hi guys,

I switched up to using ODATA. The list ist now 6.5k items, the report refreshes in about 5-6 minutes.
It works fine, not as fast as using thr built in SP connector though.
Also, I have not yet explored the solution posted by @ppm1 - no doubt that it is also a great method.

The best thing about ODATA that I noticed is that it extracts values from complex columns from the get-go (expands in other term), so you don't have to use costly extract steps. Try it.

[ppm1]

Glad you got an approach that's working. FYI that the REST API approach also allows you to expand lookup column data using $expand.

Pat

[Syndicate_Admin]

Hi, have you found a way to resolve this issue. I'm experiencing the same.

[ppm1]

Please see this blog/video for another (and much faster) way to pull in SharePoint list data.

Get Data From SharePoint Lists … But Fast – Hoosier BI

Pat