Forum Discussion

dm1904's avatar
dm1904
Helper I
6 years ago

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

Hi,   My solution had been working fine for months, until 3 days ago when my reports started failing. The datasource is SharePoint Online Lists and I have 4 different reports querying the same Shar...
Need Help
otravers's avatar
otravers
Community Champion
6 years ago

SharePoint is a convenient data source for Power BI which I use often, but I find it slow and less than entirely reliable. You might want to try the followin:

 

1. Remove and reestablish the SharePoint credentials in the Power BI service (where you set up dataset scheduled refreshes).

 

2. Download the CSV file with more details about the refresh error.

 

3. Check that there are no values in the SP list that lead to a failure in Power Query such as an invalid values vs. the expected data type (like text in a column set to Date type).

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Team,

     

    My advice for Power BI > Sharepoint Lists

     

    1. Use Odata.Feed (Not default SP connector) and use $SELECT to get only the fields you need

    2. If your lists have >500 records, then do not use the field FIELDVALUEASTEXT with expansion as that is a JSON summary field that significantly bloats the RESPONSE payload 

    3. Make sure that entire M code script runs in under 45 minutes as the AUTHORIZATION token will expire after 60 minutes and you will get wierd "NOT AUTHORIZED" errors

     

    HTH,

     

    Jim

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Jim,

      I'm getting the same error, but pulling together excel files in a SP folder, doing some transforms and appending... can I also use the OData connector? I've never tried that. 

       

      Thanks,

      Kim