SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error.

[JakeSWTC]

Hi,

One of my Power BI reports suddenly stopped working and gave me the following error when updating:

SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error. (The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator.)

From what I can find there is some sort of limit in SharePoint to 5000 records.  Is there a way to fix this?  None of the other posts I've been able to find have a solution.

Thank you

[MCY]

Hi Anonymous,

I met exactly the same issue for one list whether other lists in the same SP site works perfectly. After two days I've found a workaround by
1) graphical user interface : Get Data -> SharePoint Online list -> select option "Implementation 2.0" before entered your Site URL -> OK.
2) power query : = SharePoint.Tables("YOUR_SITE_URL", [Implementation="2.0", ViewMode="Default"])

Hope it will help 😉

[magicbiman]

Hi @JakeSWTC ,

I managed to resolve the same error in our situation by using an OData Query to directly request the columns from the document library that I required for reporting. 

https://<SharePoint Domain>/sites/<Site Name>/_api/web/lists/getByTitle('<SharePoint Library Name>')/items?$select=<Field1>,<Field2>,<LookupField1>/<FieldToBeExpanded1>,<LookupField2>/<FieldToBeExpanded2>&$expand=<LookupField1>,<LookupField2>

To avoid having to rebuild your entire report from scratch with the new data source, you can potentially replace the existing SharePoint Document Library query in your current report by doing the following:

1. Identify and note down all of the SharePoint Library Columns you require in your report (i.e. click on each visual and identify each field you are actually using). Remember, spaces in sharepoint column names will be represented as _x0020_ and lookup fields can be expanded as detailed above.

2. Build your OData Query with the required fields. First test the url with just a title field just to make sure the connection is working then add the additional fields as appropriate.

https://<SharePoint Domain>/sites/<Site Name>/_api/web/lists/getByTitle('<SharePoint Library Name>')/items?$select=Title

3. Create a new table in your Power BI Query using the Get OData request. Once created, look at the advanced query editor and copy the query that you have build.

Get Data Using an OData Query

4. Replace the initial data query in your current tables with the new query using the advanced query editor and remove any steps that aren't required. You should hopefully be left with all the columns that were originally in use in your report without having to rebuild any visuals.

Should hopefully be able to see the data is pulled in as appropriate and the query should run much faster than before if its a large document library as you are only requesting the fields that you require to run the reports you need.

Hope this helps!

[JakeSWTC]

As of 09/23/19 the errors went away without any action taken by me.  I'm not sure if something changed on Microsoft's end.

[magicbiman]

This same thing has happened to me today too without any change to the document library. Sounds like microsoft may have reduced the lookup column limit in SharePoint Online?

[dax]

Hi JakeSWTC,,
It seems to be problem in sharepoint, you could try to increase lookup column in sharepoint setting. You could refer to How to increase Lookup column limit from 8 and How to increase lookup column limit in SharePoint lists for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.