Power BI Desktop failed to get data from Sharepoint

[RaviNadipalli]

Hi,

I'm using the below to extract data from Sharepoint in Power BI Desktop (Power Query Editor).

= Json.Document(Web.Contents("https://XXX.sharepoint.com/_api/search/query?querytext='SPContentType:Property'&trimduplicates=false...'",
[Headers=[Accept="application/json"]]))

It was running perfectly fine until yesterday, but started failing from today with below error.

DataSource.Error: Web.Contents failed to get contents from 'https://XXX.sharepoint.com/_api/search/query?querytext=%27SPContentType%3AProperty%27&trimduplicates...' (500): Internal Server Error
Details:
DataSourceKind=Web
DataSourcePath=https://XXX.sharepoint.com/_api/search/query
Url=https://XXX.sharepoint.com/_api/search/query?querytext=%27SPContentType%3AProperty%27&trimduplicates...

Everytime it is giving error with a different Startrow. 

Can someone help me figure out the solution for this issu

[v-luwang-msft]

Hi @RaviNadipalli ，

Similar error code about connect to sharepoint refer:

https://community.powerbi.com/t5/Desktop/DataSource-Error-Web-Contents-failed-to-get-data-from-500/m... 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[mahoneypat]

Please see this video for a different and faster way to pull data from a big SharePoint list.

Get SharePoint List Data with Power BI ... Fast - YouTube

Pat

[RaviNadipalli]

Hi Pat,

Thanks for your reply.  

I'm already using the REST Api if you observe my query starting with below.

= Json.Document(Web.Contents("https://XXX.sharepoint.com/_api/search/query

Even in the video you shared, he was suggesting to use REST Api for better performance, which I'm already using. As I mentioned earlier, my solution was working for years but started failing recently.

Sorry for late reply. Thanks.

-Ravi

[amitchandak]

@RaviNadipalli , to me, seems like a problem with url.

Can you take the URL once again and update the data source using the data source setting. Also use clear permissions and provide credentials again

[RaviNadipalli]

Thanks Amit for your reply.

I tried doing both updating the URL in Data source settings and also Clear permissions and provided the credentials, but no luck. It is still failing to refresh. 

We are pulling 500 records from Sharepoint in every iteration and we are repeating this process to pull the entire data. This process was running for last couple of years and there's no code change too. But it is failing now, and shows a different Startrow every time it fails. That means sometimes it fails at 1500th record and sometimes 6500th record and sometimes 3000th record etc.. 

It is throwing generic "(500): Internal Server Error" error as shown below.

Looks like the URLs are getting trimmed in this post. I'm giving the URL again (except https prefix) to see if it displays properly for your reference.

DataSource.Error: Web.Contents failed to get contents from 'XXX.sharepoint.com/_api/search/query?que...

Everyone,

My client need this to be solved asap. Any help is much appreciated!!!

Regards,

Ravi