cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
S_Lee Frequent Visitor
Frequent Visitor

Getting Data from SharePoint List -Error (500) Internal Server Error and OData Version 3, 4 Errors

Hi all,

 

Trying to solve a issue using the SharePoint List connector using Power BI Desktop (I'm using the Dec 2018 update).

 

Here's the situation:

I have a SharePoint List with Lookup and Choice Columns. No Calculated Columns, and all column names do not begin with numbers. This List was created on SharePoint Online (365), as part of a proof-of-concept phase, and connecting with the SharePoint List connector in Power BI Desktop with ApiVersion = 15, works fine without issues, data is previewed and can be loaded into the data model. So, all good when using SharePoint Online (365).


Now here's the issue - when creating the same list on SharePoint 2016 (On-Premises), accessing the List with the exact same SharePoint List connector is throwing the following error message:

 

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error. (The given key was not present in the dictionary.)
Details:
DataSourceKind=SharePoint
DataSourcePath=https://zzzzz.zzz.com/sites/mysitecollection
SPRequestGuid=.....................
Url=https://zzzzz.zzz.com/sites/mysitecollection/_api/Web/Lists(guid'.....................')/Items

 

This is puzzling, as the List can be viewed thru the web browser without any issues. So permissioning doesn't appear to be problem. Simpler Lists, without Lookup or Choice Columns can be queried with the Connector without issues.

 

I've tried:
1. Clearing the Power BI Desktop Data Load cache
2. Changing to APIVersion = 14 for the SharePoint List Connector, but I can no longer select the List of interest
3. Switching to OData Feed data source (Version 2.0) without success - I'm now getting OData errors as follows-

 

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (500) Internal Server Error. (The given key was not present in the dictionary.)
OData Version: 4, Error: The remote server returned an error: (500) Internal Server Error. (The given key was not present in the dictionary.)
OData Version: 3, Error: The remote server returned an error: (500) Internal Server Error. (The given key was not present in the dictionary.)
Details:
DataSourceKind=OData
DataSourcePath=https://zzzzz.zzz.com/sites/mysitecollection/_api/lists/getbytitle('.....................')/items


Can anyone help - it's not clear why the same process works fine with the List on SharePoint Online, but cause so many issues when on SharePoint 2016 (On-Premises). Also, changing the List to exclude Lookups and Choice columns is not possible.

 

Many thanks!

Lee

 

 

2 REPLIES 2
Highlighted
Community Support Team
Community Support Team

Re: Getting Data from SharePoint List -Error (500) Internal Server Error and OData Version 3, 4 Erro

Hi @S_Lee ,

Please update your Power BI Desktop to be the latest version 2.67.5404.801 (March 2019) and try again. 

In addition, please have a test if you could use the SharePoint online url with the SharePoint List connector.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
S_Lee Frequent Visitor
Frequent Visitor

Re: Getting Data from SharePoint List -Error (500) Internal Server Error and OData Version 3, 4 Erro

Hi,

 

No effect - can anyone help?

 

Thanks in advance