Error Connectig to on Prem SharePoint List (SharePoint List & OData Connectors)

[Anonymous]

I am attempting to connect to a on premises SharePoint List with Power Query in PowerBI desktop. (Version 2.69.5467.2151 64-bit (May 2019). I get errors attempting to connect to this SharePoint list using both a

• SharePoint List Connector and
• OData Connection.

I am looking for any suggestions or workarrounds that may work. Here is what I have tried. 

1) Using SharePoint List Connector

Using the SharePoint List, I can successfully connect to the list (see screenshot below)

However, when I select the list to load, I get the  error  (see error message 1 below). This error suggests an error with the property “wd8old”.  Online searchs (see here)  returns suggestions to “put a default value into this field”. However, in our situation this field isn’t visible in the underlying SharePoint list (Through the GUI anyway).

ERROR  Message 1

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] We couldn't parse OData response result.  Error: A null value was found for the property named 'wd8oId', which has the expected type 'Collection(Edm.Int32)[Nullable=False]'. The expected type 'Collection(Edm.Int32)[Nullable=False]' does not allow null values.

Another suggestion (also in the link above) is to  change the API version from 15 to 14.  But this results in another Error  (error message 2 below)

ERROR Message 2

Expression>Error: The key didn't match any rows in the table. Details: Key=Record Table = Table

2) using an OData Connector

When I attempt to connect to the list with an Odata with the following URL

https://teams. ABCBusiness/sites/ra/_vti_bin/listdata.svc     Note : the “ /_vti_bin/listdata.svc “ is a suffix that I am adding to the URL root https://teams. ABCBusiness/sites/ra/   . In this case I get another error

ERROR  Message 3 

DataSource.Error: OData: Request failed: The remote server returned an error: (500) Internal Server Error. (An error occurred while processing this request.). Details:     DataSourceKind=OData    DataSourcePath=https://teams.XXXXXXX/sites/ra/_vti_bin/listdata.svc/AuditRecommendationsTracking  SPRequestGuid=987de39e-a844-d040-a6f6-c49924d3e801

Searching online for the 500 internal server error (which in itself if a very genera Http status code), leads to lots of general suggestions (e.g. deleting columns from the SharePoint site () which I can’t do, as I don’t have visibility into what fields are used by others).  but not solution.

Does anyone have a suggestion on how to resolve these errors, and connect to the Sharepoint list?

Does anyone have a suggestion on how to resolve these errors, and connect to the Sharepoint list?

[v-juanli-msft]

Hi @Anonymous 

1.Using SharePoint List Connector

Try these methods to see if they help:

Way 1: delete the connection and cache in power bi

(Under data source setting, clear permission for that data source,

under File->options and settings->option->data load->clear cache),

reconnect to sharepoint list with power bi desktop, change the API version from 15 to 14 in Advanced editor.

Way 2: In Steps pane of power query, delete the 'Navigation' step and then click close and apply

2.using an OData Connector

If you try to Connect to specific SharePoint List in a Team SharePoint Site with Power BI Desktop,

Please input the URL in the OData entry in Power BI Desktop.
https://yoursharepointsite/_vti_bin/listdata.svc/ListName

Or try this suggestion:

Error with People or Group Field when using Power BI with SharePoint Online

Last but not least, please update to last version: May 2019 Update (2.69.5467.1251)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-juanli-msft  Hi Maggi, Thanks for the suggestions. I have tried each of the 4 suggestions. Unfortunately none worked 😞  Any other ideas/ Suggestions?

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Sorry for not helping you efficiently.

If you have a pro account, you could create a free support ticke to get more quick support.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.