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

Hi Anonymous ,

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!