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
S_Lee
New Member

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

 

 

5 REPLIES 5
Anonymous
Not applicable

Were you guys able to solve this issue?? I am facing the same issue but only on one of my list columns that is a person multiple lookup field.

MaríaIgnacia
Regular Visitor

I have the same problem, any solutions?

v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Hi,

 

No effect - can anyone help?

 

Thanks in advance

I´m facing the same error here.

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