Internal Server Error while importing SharePoint Online List

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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?

[Syndicate_Admin]

Hi @jamieo,

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

Thanks,
Lydia Zhang

[Syndicate_Admin]

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

[Syndicate_Admin]

I am also facing a similar problem...

[Syndicate_Admin]

Did you get the solution to this ? 

@jamieo

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

[Syndicate_Admin]

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

[Syndicate_Admin]

Hi - was there any solution to this

[Syndicate_Admin]

Hi all,

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

best,

J.