Forum Discussion

jamieo's avatar
jamieo
Microsoft Employee
9 years ago

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

  • dvnshshrma's avatar
    dvnshshrma
    Regular Visitor
    9 years ago

    Did you get the solution to this ? 

    jamieo

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

    • jamieo's avatar
      jamieo
      Microsoft Employee
      9 years ago

      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":"https://microsoft.sharepoint.com/teams/Revolution_edu_team/_vti_bin/ListData.svc","RequestHeaders":"null","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.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=1000","RequestMethod":"GET","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\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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi jamieo,

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

        Thanks,
        Lydia Zhang

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

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

       

  • Jmenas's avatar
    Jmenas
    Advocate III
    8 years ago

    Hi all,

     

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

     

    best,

    J.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.  

  • NAOS's avatar
    NAOS
    Advocate III
    6 years ago

    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 😉

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      4 years ago

      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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    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