Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jamieo
Employee
Employee

Internal Server Error while importing SharePoint Online List

I need to import a SharePoint Online List with Collection values, so I need to use ApiVersion 14.  Recently, this stopped working and started giving errors.  I'm not sure if it's relevant, but the list shows up as "TeamTracking" with ApiVersion 14, but "Team Tracking" with ApiVersion 15 (but cannot be loaded due to the Collection type).

 

 

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error. (An error occurred while processing this request.)
Details:
DataSourceKind=SharePoint
DataSourcePath=https://microsoft.sharepoint.com/teams/XXX
SPRequestGuid=9e70b79d-1049-0000-4e50-6e555bc44da6
Url=https://microsoft.sharepoint.com/teams/XXX/XXX

17 REPLIES 17
Syndicate_Admin
Administrator
Administrator

i tried here apiversion 15 , but problem is which is lookup columns , not showing any options like date,title ..etc , i feel like segregated entire data into different tables , i just convinced to my managers what are limitations in bi

Syndicate_Admin
Administrator
Administrator

Hi Dan, no, no reply. Not that I was expecting one...

NAOS
Helper IV
Helper IV

Hi everyone!

I was having the exact same issue with Sharepoint server 2013, and connecting using the Sharepoint list connector (Sharepoint.Tables, ApiVersion 14 in Power Query editor).

My table was working fine with the version 15 of the Api but would not work for the Version 14, which I need so dates stay the same as in Sharepoint.

My solution (it may not be everyone's case but I hope this helps someone else too) was to delete a Calculated column, which in this case gave as result html and icons which where veing queried from a library in the site.
Again, this may not be everyone's problem but it definetely is one of the possible causes, so it is worth checking 😉

Somewhat belated, but here is my workaround. In my case I needed to load the files from a SP Folder and be able to use the create/update dates in local (AUS) format. Using ApiVersion 15 resulted in parse errors on the date columns because they are not loaded with local format, so any day number > 12 is invalid. Using ApiVersion 14 loads the dates in the correct local format but causes internal Server Error 500. What I noticed was the error appeared as a row after the last row of filenames in the table. But unfortunately you cannot remove this row with a Table.RemoveRowsWithErrors statement (I guess because it is not really a row in the table). It occurred to me that I might be able to load the TopN rows with ApiVersion 14, where N is simply the number of rows that I determine using a Table.RowCount statement that uses ApiVersion 15. A tad convoluted, but bippity boppity boo, all my rows were loaded without an error, albeit without last access date:

 

let

    #"SP Folder" = "",

    Source = Table.FirstN(SharePoint.Files(#"SP Folder", [ApiVersion = 14]), Table.RowCount(SharePoint.Files(#"SP Folder", [ApiVersion = 15])))

in

    Source

Jmenas
Advocate III
Advocate III

Hi all,

 

I am having the issue here. Any news or if there is a Bug report for this?

 

best,

J.

been ages but same issue today

Anonymous
Not applicable

Still same issue no response or resolution.   I am seriously thinking moving out of sharepoint to some other source. 

 

@Microsoft :  Please fix this issue.  This seems like a bug, there are many threads around this issue but no resolution yet to this problem.  This is happening to specific sharepoint site and not for all.  

Hello did you get any response to this?

Anonymous
Not applicable

I've got the same issue. It was caused by the the 5000 item limit on Sharepoint view.

more then 5k records , we can approach odatafeed or web connectors , but not sure these connectors how much satisfy your requirements 

 

dvnshshrma
Regular Visitor

Did you get the solution to this ? 

@jamieo

I am stuck on a similar issue. If you have, Please Reply,.

ksingla96
Frequent Visitor

I am also facing a similar problem...

v-yuezhe-msft
Employee
Employee

Hi @jamieo,

In Power BI Desktop, please go to File -> Options and settings -> Options -> Diagnostics and click "enable tracing", then connect to SharePoint Online list and check the detailed logs in the traces folder. Also please help to post the detailed logs here. You can also use Fiddler to monitor the Http traffic between Power BI Desktop and SharePoint, and check what is going on in the background.

Additionally, do you get the following error message when using ApiVersion 15? If that is the case, fill these null values to temporarily work around this issue.

“DataSource.Error: We couldn't parse OData response result.  Error: A null value was found for the property named 'Actionee1Id', which has the expected type 'Collection(Edm.Int32)[Nullable=False]'. The expected type 'Collection(Edm.Int32)[Nullable=False]' does not allow null values.”

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Here's the Trace logs:

 

DataMashup.Trace Information: 24579 : {"Start":"2016-11-16T16:07:39.3423215Z","Action":"Engine/IO/OData/Create","ServiceUri":"https://microsoft.sharepoint.com/teams/Revolution_edu_team/_vti_bin/ListData.svc","RequestUrl":"http... (PBIDesktop)","ActivityId":"6c237b2a-0034-495d-b503-53ee9551bb45","Process":"Microsoft.Mashup.Container.NetFX40","Pid":13124,"Tid":1,"Duration":"00:00:00.4101442"}
DataMashup.Trace Information: 24579 : {"Start":"2016-11-16T16:07:39.3310555Z","Action":"SimpleDocumentEvaluator/GetResult/Evaluate","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"6c237b2a-0034-495d-b503-53ee9551bb45","Process":"Microsoft.Mashup.Container.NetFX40","Pid":13124,"Tid":1,"Duration":"00:00:00.4218350"}
DataMashup.Trace Information: 24579 : {"Start":"2016-11-16T16:07:39.7529449Z","Action":"BackgroundThread/RollingTraceWriter/Flush","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX40","Pid":13124,"Tid":12,"Duration":"00:00:00.0016985"}
DataMashup.Trace Information: 24579 : {"Start":"2016-11-16T16:07:41.3091112Z","Action":"Engine/IO/OData/Request","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"6c237b2a-0034-495d-b503-53ee9551bb45","Process":"Microsoft.Mashup.Container.NetFX40","Pid":13124,"Tid":1,"Duration":"00:00:00.0001039"}
DataMashup.Trace Error: 24579 : {"Start":"2016-11-16T16:07:42.3301344Z","Action":"Engine/IO/OData/GetResponse","RequestUri":"https://microsoft.sharepoint.com/teams/Revolution_edu_team/_vti_bin/ListData.svc/TeamTracking?$top=1...: System.Net.WebException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: The remote server returned an error: (500) Internal Server Error.\r\nStackTrace:\n at System.Net.HttpWebRequest.GetResponse()\r\n at Microsoft.Mashup.Engine1.Library.OData.ODataRequest.GetResponse(WebRequest webRequest)\r\n\r\n\r\n","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"6c237b2a-0034-495d-b503-53ee9551bb45","Process":"Microsoft.Mashup.Container.NetFX40","Pid":13124,"Tid":1,"Duration":"00:00:00.9034242"}
DataMashup.Trace Warning: 24579 : {"Start":"2016-11-16T16:07:41.2890643Z","Action":"Library/Common/RetryPolicy/TryExecuteAction","Attempt":"1","Exception":"Exception:\r\nExceptionType: System.Net.WebException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: The remote server returned an error: (500) Internal Server Error.\r\nStackTrace:\n at System.Net.HttpWebRequest.GetResponse()\r\n at Microsoft.Mashup.Engine1.Library.OData.ODataRequest.GetResponse(WebRequest webRequest)\r\n at Microsoft.Mashup.Engine1.Library.Web.Request.ManualRedirect(IEngineHost host, String resourceKind, Uri uri, CreateWebRequestDelegate createRequest, Func`2 createResponse, WebRequest& webRequest, Int32[] nonErrors)\r\n at Microsoft.Mashup.Engine1.Library.OData.ODataRequest.<>c__DisplayClass1.<GetResponseStream>b__0()\r\n at Microsoft.Mashup.Engine1.Library.Common.RetryPolicy.Execute[TResult](IEngineHost host, Func`1 func)\r\n\r\n\r\n","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"6c237b2a-0034-495d-b503-53ee9551bb45","Process":"Microsoft.Mashup.Container.NetFX40","Pid":13124,"Tid":1,"Duration":"00:00:01.9482311"}
DataMashup.Trace Error: 24579 : {"Start":"2016-11-16T16:07:41.2884307Z","Action":"Library/Common/RetryPolicy/ExecuteAction","TryCount":"3","RetryAlgorithm":"ExponentialBackoff","Exception":"Exception:\r\nExceptionType: System.Net.WebException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: The remote server returned an error: (500) Internal Server Error.\r\nStackTrace:\n at System.Net.HttpWebRequest.GetResponse()\r\n at Microsoft.Mashup.Engine1.Library.OData.ODataRequest.GetResponse(WebRequest webRequest)\r\n at Microsoft.Mashup.Engine1.Library.Web.Request.ManualRedirect(IEngineHost host, String resourceKind, Uri uri, CreateWebRequestDelegate createRequest, Func`2 createResponse, WebRequest& webRequest, Int32[] nonErrors)\r\n at Microsoft.Mashup.Engine1.Library.OData.ODataRequest.<>c__DisplayClass1.<GetResponseStream>b__0()\r\n at Microsoft.Mashup.Engine1.Library.Common.RetryPolicy.Execute[TResult](IEngineHost host, Func`1 func)\r\n\r\n\r\n","ProductVersion":"2.40.4554.463 (PBIDesktop)","ActivityId":"6c237b2a-0034-495d-b503-53ee9551bb45","Process":"Microsoft.Mashup.Container.NetFX40","Pid":13124,"Tid":1,"Duration":"00:00:01.9491566"}

 

That's the error I get with version 15, but how do I create a non-null but empty Collection value?

Hi @jamieo,

Have you used fiddler to capture the Http traffic between Power BI and Sharepoint List?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi - was there any solution to this

Here's what I see in Fiddler:

 

CONNECT microsoft.sharepoint.com:443 HTTP/1.1
Host: microsoft.sharepoint.com
Connection: Keep-Alive

A SSLv3-compatible ClientHello handshake was found. Fiddler extracted the parameters below.

Version: 3.3 (TLS/1.2)
Random: 58 33 29 57 38 F1 A6 2F 89 F5 D9 47 D8 93 85 17 3A 68 E8 35 C1 54 60 4C E9 E8 93 75 E5 09 72 AE
"Time": 5/3/2016 6:25:12 PM
SessionID: C7 09 00 00 FC EC 3E 4A 44 9B A8 7A CE 1B 6F 67 87 95 FD 64 26 0F 18 04 4F 72 3E FC 24 4B 71 2E
Extensions:
server_name microsoft.sharepoint.com
elliptic_curves secp256r1 [0x17], secp384r1 [0x18]
ec_point_formats uncompressed [0x0]
signature_algs sha512_rsa, sha512_ecdsa, sha256_rsa, sha384_rsa, sha1_rsa, sha256_ecdsa, sha384_ecdsa, sha1_ecdsa, sha1_dsa
SessionTicket empty
extended_master_secret empty
renegotiation_info 00
Ciphers:
[C02C] TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384
[C02B] TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256
[C030] TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
[C02F] TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
[009F] TLS_DHE_RSA_WITH_AES_256_GCM_SHA384
[009E] TLS_DHE_RSA_WITH_AES_128_GCM_SHA256
[C024] TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384
[C023] TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256
[C028] TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384
[C027] TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256
[C00A] TLS1_CK_ECDHE_ECDSA_WITH_AES_256_CBC_SHA
[C009] TLS1_CK_ECDHE_ECDSA_WITH_AES_128_CBC_SHA
[C014] TLS1_CK_ECDHE_RSA_WITH_AES_256_CBC_SHA
[C013] TLS1_CK_ECDHE_RSA_WITH_AES_128_CBC_SHA
[0039] TLS_DHE_RSA_WITH_AES_256_SHA
[0033] TLS_DHE_RSA_WITH_AES_128_SHA
[009D] TLS_RSA_WITH_AES_256_GCM_SHA384
[009C] TLS_RSA_WITH_AES_128_GCM_SHA256
[003D] TLS_RSA_WITH_AES_256_CBC_SHA256
[003C] TLS_RSA_WITH_AES_128_CBC_SHA256
[0035] TLS_RSA_AES_256_SHA
[002F] TLS_RSA_AES_128_SHA
[000A] SSL_RSA_WITH_3DES_EDE_SHA
[006A] TLS_DHE_DSS_WITH_AES_256_CBC_SHA256
[0040] TLS_DHE_DSS_WITH_AES_128_CBC_SHA256
[0038] TLS_DHE_DSS_WITH_AES_256_SHA
[0032] TLS_DHE_DSS_WITH_AES_128_SHA
[0013] SSL_DHE_DSS_WITH_3DES_EDE_SHA
[0005] SSL_RSA_WITH_RC4_128_SHA
[0004] SSL_RSA_WITH_RC4_128_MD5

Compression:
[00] NO_COMPRESSION

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors