SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error.

[dm1904]

Hi,

My solution had been working fine for months, until 3 days ago when my reports started failing. The datasource is SharePoint Online Lists and I have 4 different reports querying the same SharePoint site throughout the day (Within the Pro scheduled refresh limits).

Can I anybody suggest what could be happening. I've ruled out a problem with the data, could this be a SharePoint throttle issue?

"SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error. (Internal Server Error). The exception was raised by the IDataReader interface. Table:"

[otravers]

SharePoint is a convenient data source for Power BI which I use often, but I find it slow and less than entirely reliable. You might want to try the followin:

1. Remove and reestablish the SharePoint credentials in the Power BI service (where you set up dataset scheduled refreshes).

2. Download the CSV file with more details about the refresh error.

3. Check that there are no values in the SP list that lead to a failure in Power Query such as an invalid values vs. the expected data type (like text in a column set to Date type).

[Anonymous]

Team,

My advice for Power BI > Sharepoint Lists

1. Use Odata.Feed (Not default SP connector) and use $SELECT to get only the fields you need

2. If your lists have >500 records, then do not use the field FIELDVALUEASTEXT with expansion as that is a JSON summary field that significantly bloats the RESPONSE payload 

3. Make sure that entire M code script runs in under 45 minutes as the AUTHORIZATION token will expire after 60 minutes and you will get wierd "NOT AUTHORIZED" errors

HTH,

Jim

[Anonymous]

Hi Jim,

I'm getting the same error, but pulling together excel files in a SP folder, doing some transforms and appending... can I also use the OData connector? I've never tried that. 

Thanks,

Kim

[Anonymous]

having the same issue - checking in to see if there are any resolutions.

[GilbertQ]

It would appear that there might be an issue with the way the refresh is happening?

I am assuming because it is SharePoint online that you are not using the On-Premise Data Gateway?

If not I would suggest to log a support ticket for further investigation.

[dm1904]

Yes @GilbertQ  all SharePoint online. Never had this problem before and strangely it is happening most during working hours. Of an evening and early in the mornings, the solution refreshes as normal. How do i raise a support ticket?

[GilbertQ]

Hi there

That does appear to be really odd.

You can use my blog post on how to raise a support ticket: https://www.fourmoo.com/2019/09/24/using-power-bi-support-to-create-a-support-ticket/

[Anonymous]

Team,

We are having the same exact issue -- did you ever get a root cause from Microsoft?

Jim

[dm1904]

Hi @Anonymous  I did get the below from the Microsoft support team in India on 21st Nov. No update or fix yet from what I can tell. I also found an issue with Dataflow entities being invisible to the PBI connector if they used Sharepoint files as a source. Microsoft said this is related to the same issue. My solutions seem to refresh ok outside of UK working hours. Not great if you need more regular refreshes like me, but I have no choice but to scale back until Microsoft resolve!

"Many thanks for reaching out to Microsoft support team, the issue you are experiencing is due to an unexpected defect surfaced on our end  which caused service outage in SharePoint  System. We regret the inconvenience caused. 

SharePoint Engineering is currently investigating the issue. We will keep you posted as and we have an update from them on the same. 

Our sincere apology for the delay and inconvenience caused by this outage."

[Anonymous]

Have you received any feedback? We are experiencing the same issue since 15 November and have not yet received any update on our incident.

[dm1904]

@Anonymous  just that it mut definately be a sharepoint issue. There seems to be issues where Power BI, Flow, OneDrive connect to SharePoint online. My ticket was escalated to the SharePoint team and I've had no further feedback. However, my reports seem to be refreshing more normally today so fingers crossed the issue is resolved

[ignaciops]

We are facing the same issue. Do you have news from support team?