Forum Discussion

RaviNadipalli's avatar
RaviNadipalli
New Member
4 years ago

Power BI Desktop failed to get data from Sharepoint

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='SPConte...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
RaviNadipalli
New Member
4 years ago

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?querytext=%27SPContentType%3AProperty%27&trimduplicates=false&rowlimit=500&selectproperties=%27Title%2CComplianceDateOWSDATE%2CCountryOWSCHCS%2CPropertyStateOWSTEXT%2CSiteURLOWSURLH%2CIsCompliantOWSBOOL%2CClientNameOWSTEXT%2CPath%2CowstaxIdPropertyType%2CIsCEEProgramCompleteOWSBOOL%2COBSRPTDueDate%2CSITRPTDueDate%2CPropertyStatusOWSCHCS%27&startrow=1500' (500): Internal Server Error
Details:
DataSourceKind=Web
DataSourcePath=https://XXX.sharepoint.com/_api/search/query
Url=XXX.sharepoint.com/_api/search/query?querytext=%27SPContentType%3AProperty%27&trimduplicates=false&rowlimit=500&selectproperties=%27Title%2CComplianceDateOWSDATE%2CCountryOWSCHCS%2CPropertyStateOWSTEXT%2CSiteURLOWSURLH%2CIsCompliantOWSBOOL%2CClientNameOWSTEXT%2CPath%2CowstaxIdPropertyType%2CIsCEEProgramCompleteOWSBOOL%2COBSRPTDueDate%2CSITRPTDueDate%2CPropertyStatusOWSCHCS%27&startrow=1500XXX.sharepoint.com/_api/search/query?querytext=%27SPContentType%3AProperty%27&trimduplicates=false&rowlimit=500&selectproperties=%27Title%2CComplianceDateOWSDATE%2CCountryOWSCHCS%2CPropertyStateOWSTEXT%2CSiteURLOWSURLH%2CIsCompliantOWSBOOL%2CClientNameOWSTEXT%2CPath%2CowstaxIdPropertyType%2CIsCEEProgramCompleteOWSBOOL%2COBSRPTDueDate%2CSITRPTDueDate%2CPropertyStatusOWSCHCS%27&startrow=1500

 

Everyone,

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

 

Regards,

Ravi