Connecting to SPO list via OData

[Anonymous]

Hi everyone

I've tried to connect to a SharePoint Online list in Power BI Desktop but am getting an error saying that it exceeds the lookup threshold (there are lots of people picker fields in the list).  I've read that a way around this is to connect to the list using OData.

However, I'm having trouble connecting using OData.  I've entered the URL which is formatted as https//siteaddress//_vti_bin/listdata.svc and this brings up a list of all the lists and fields in this site.  When I try to load the list I want I get the error below:

DataSource.Error: OData: Request failed: The remote server returned an error: (500) Internal Server Error. (An error occurred while processing this request.)

I've looked and can't find anything about how I can resoolve this issue.  Some posts have mentioned calculated fields being a problem, and my list does have 2 of these, but I'm not sure if this is what the problem is?  I don't think it's permission related as the account I'm using in Power BI is able to access the site and list in SharePoint.  I'd be grateful for any advice anyone has about how I could overcome this?

Thanks in advance.

[v-frfei-msft]

Hi @Anonymous,

• Open Power BI >  Click on Get Data > OData Feed.
• Set the Url to https://rootsite/_vti_bin/listdata.svc. (https://xxxxxxx.sharepoint.cn/sites/xxx/UatTest/_vti_bin/listdata.svc)
• Set the credential to windows authentication , it's not worked with anonymous authontication.
• Now it's should connected.
• Select your library that you need to retrieve its data .
• Click Load, now it's should be loaded properly !

Regards,

Frank

[Anonymous]

Hi Frank

Thanks for the quick reply.  I tried the steps you listed but regardless of whether I select Windows or Organizational authentication I'm getting the error "Access to the resource is forbidden"

I have tried connecting with a few different accounts that all have full access to the SharePoint site I'm trying to connect to.  The only other thing I can think of that may be affecting this is that we use ADFS to authenticate with Office 365.  Would the steps be any different in this scenario?

[v-frfei-msft]

Hi @Anonymous,

Did you add "_vti_bin/listdata.svc" after the URL? 

Regards,

Frank

[Anonymous]

Yes, the full URL is https://tenant.sharepoint.com/site/subsite/_vti_bin/listdata.svc

[v-frfei-msft]

Hi @Anonymous,

The above error could occur when using wrong log-in option to connect to SharePoint.

If your site is Office365, please enter organizational credential using the third option in the above screenshot. If your site is on premise, use Windows credentials as you mentioned. In addition, make sure that you use the latest version of Power BI Desktop.

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft

Thanks for that.  I've updated my PBI Desktop and am now able to connect to the site using the OData URL.  I think I've narrowed down the problem to the one list, as I can conncet to any other list that appears in the OData connection.  if I try to connect to the one list I need to though I get the original error I was getting:

DataSource.Error: OData: Request failed: The remote server returned an error: (500) Internal Server Error. (An error occurred while processing this request.)

This list is the one I can't connect to using the in-built SharePoint Online List connector because of the lookup threshold.  However, I was under the impression that connecting via OData would get around this?  The error message is very vague so is there a way of getting further information about it?

[v-frfei-msft]

Hi @Anonymous,

Does the list have any calculated columns? If so then PowerPivot cannot connect to such list. Kindly delete the calculated columns and try again.

Regards,

Frank

[Anonymous]

@v-frfei-msftthere was a calculated column in this list and I've now removed that but still getting the error.  I've also tried entering the ODate URL into a browser and get the following:

<?xml version="1.0" encoding="ISO-8859-1"?>

<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> <code/> <message xml:lang="en-GB">An error occurred while processing this request.</message> </error>

The only other thing I can think it could be is that there are 19 lookup fields in the list (mainly people fields) so could this be causing the problem?  I know there's a limit to lookup fields in Power BI as I couldn't add this list using the SharePoint list connector for that reason.  However, I thought using OData was a way around this problem?

[Anonymous · Accepted solution]

I've finally narrowed down the issue.  My list had 15 people picker fields that were set to allow multiple selections.  I changed these fields to not allow multiple selections and then was able to connect to the list using OData in Power BI Desktop.  I assume there must be a limit to how many multiple selection people picker fields are allowed as it started working once the number of fields with multiple selections got down to 11?

Thanks to everyone for your help with this.