Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
I have the same problem, any solutions?
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
Hi,
No effect - can anyone help?
Thanks in advance
I´m facing the same error here.