DataSource.Error Web.Contents failed to get data from (500): Internal Server Error

[Amratya]

Hi Everyone,

When I'm trying to refresh my data I got this error

An error occurred in the ‘Sample File’ query. DataSource.Error: Web.Contents failed to get contents from 'https://xxxx-my.sharepoint.com/personal/xxxx/_api/web/lists/getbyid('bc70fb34-3f97-4025-af5d-03235fa33b8e')/RenderListDataAsStream' (500): Internal Server Error
Details:
    DataSourceKind=SharePoint
    DataSourcePath=https://xxxx-my.sharepoint.com/personal/xxxxx
    Url=https://xxxx-my.sharepoint.com/personal/xxxx/_api/web/lists/getbyid('bc70fb34-3f97-4025-af5d-03235fa33b8e')/RenderListDataAsStream

and here below the M editor

let
    Source = SharePoint.Files("https://xxxxx-my.sharepoint.com/personal/xxxxxxxx", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "file_2.csv")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
    #"Invoke Custom Function1" = Table.AddColumn(#"Removed Other Columns", "Transform File from Query1", each #"Transform File from Query1"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Query1"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Query1", Table.ColumnNames(#"Transform File from Query1"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"datahora_file", type datetime}, {"diasemana_file", type text}, {"unidade", type text}, {"caixa", type text}, {"garcomlider", type text}, {"atendimento", type number}, {"ambiente", type number}, {"cafe", type number}, {"comida", type number}, {"bebida", type number}, {"preco", type number}, {"amor", type number}, {"nome_cliente", type text}, {"email_cliente", type text}, {"comentario_cliente", type text}, {"nosso_comentario", type text}, {"data_file", type date}, {"hora_file", type number}})
in
    #"Changed Type"

I can open the sharepoint folder itself properly without any problems.

Solutions approached till now without any effect:

- I changed ApiVersion to 14 instead of 15

- I removed the ApiVersion

- I cleared the permissions and add it again

- Try to connect from scratch to the same folder, same error

Thank you

[ubilger]

For me the error has appeared when I try to import a (growing) list of Sharepoint files. At some point, the 500 error will appear, and deleting or archiving a set of files will generally eliminate the error. I suspect it is the number of files that are tried to be imported, but I am not aware of a concrete limit.

[Anonymous]

You can try disabling "parrallel query processing" and "background data" in the query options. This worked for me.

[Amratya · Accepted solution]

It seems there was a problem in the data source in addition to this ApiVersion thing.

1. Remove [ApiVersion = 15]
2. Adding the datasource [sharePoint folder link] through the advanced query editor
3. Filter files and get the desired ones

I recognized there is additional row under my filtered files [Error - Error] and it is not filtered out. So, below steps to eliminate this error record from ruining my data, and I had so many files in different dashboards. So, I went through these possibilities here

• Click on Remove Rows, then Remove Error.
• If it didn't work, Click Remove Rows and then Remove the last rowfrom below.
• If it didn't work, Click Keep Rows and then keep numbbers of rows from above.

and then folow any other steps you want to apply on your data. and it worked.

[mikhailry]

I had a similar erro, but the issue was in the filename. It has a % in the name. Make sure you don't use special symbols in the file path and filenames. 

[Timbollew]

Keep rows solution does work but I can't get apply a filter before hand to sort by date modified due to the error row.

[ovetteabejuela]

Hi Please upvote:

https://community.powerbi.com/t5/Issues/SharePoint-Folder-Connector-fails-for-API-15/idc-p/586800#M3...

[Amratya]

I figured out the problem and solved it.

I will make another comment with the solution

[iossai]

Amratya,

Please how did you solve the problem? Benn having same issue for a while now

[PaulMac]

It seems there was a problem in the data source in addition to this ApiVersion thing.

1. Remove [ApiVersion = 15]
2. Adding the datasource [sharePoint folder link] through the advanced query editor
3. Filter files and get the desired ones

I recognized there is additional row under my filtered files [Error - Error] and it is not filtered out. So, below steps to eliminate this error record from ruining my data, and I had so many files in different dashboards. So, I went through these possibilities here

• Click on Remove Rows, then Remove Error.
• If it didn't work, Click Remove Rows and then Remove the last rowfrom below.
• If it didn't work, Click Keep Rows and then keep numbbers of rows from above.

and then folow any other steps you want to apply on your data. and it worked.

[Anonymous]

old post..., but how did you fix this issue?

we had the same issue ~2 months ago, then it magically started refreshing without making any changes to the report or SharePoint.  now, the issue is back and the magic still hasn't happened.

[PaulMac]

It seems there was a problem in the data source in addition to this ApiVersion thing.

1. Remove [ApiVersion = 15]
2. Adding the datasource [sharePoint folder link] through the advanced query editor
3. Filter files and get the desired ones

I recognized there is additional row under my filtered files [Error - Error] and it is not filtered out. So, below steps to eliminate this error record from ruining my data, and I had so many files in different dashboards. So, I went through these possibilities here

• Click on Remove Rows, then Remove Error.
• If it didn't work, Click Remove Rows and then Remove the last rowfrom below.
• If it didn't work, Click Keep Rows and then keep numbbers of rows from above.

and then folow any other steps you want to apply on your data. and it worked.

[mvoelkergabel]

Hello my org is having the same issue. We chnage nothing and 1/30 refreshes and it randomly works. This has been going on from 3/22 to current