cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JakeSWTC
Regular Visitor

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

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

4 REPLIES 4
magicbiman
Frequent Visitor

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 QueryGet 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!

 

 

 

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
Frequent Visitor

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
Community Support
Community Support

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!