Sharepoint file as data source results in error row that cannot be removed

[kkr_nok]

Hi,

I am connecting to a Sharepoint file as a data source in Power BI and using Power Query to access the correct file.  I can filter the file list to the files I am looking for, but there is always an error row present that I cannot remove.

In the Source step, I have tried specifying APIVersion 14, 15 and also taking the version out altogether.  I have also tried clearing my permissions and then resetting them.  Removing the errored row doesn't work in any way that I have tried nor does attempting to replace the errors.  I have seen some older posts on this issue, but none of them helped me.  Any ideas would be appreciated.

Thanks.

[Timbollew]

I know this an old post, I have the same issue, did you ever get a solution to this?

[ThomasBoulay]

Some users of my Excel file have the same error (not me...), and the Table.RemoveRowsWithErrors doesn't work.

I tried on another Sharepoint site and the problem is the same.

[lbendlin]

Is this actually impacting you? Is there a file behind that error that you need?  If so, what sets the file apart from the others?  Do you maybe lack permissions?

[kkr_nok]

Hi.  Thanks for the reply - I tried several different attempts at removing rows.  Removing errored rows, bottom row, etc

[edhans]

Can you post your full M code that shows this break?
It looks like their might be a SharePoint corruption in that library.

You could also try changing SharePoint.Files() in the Source line to SharePoint.Contents() if your data is in one folder. Then navigate to that folder. If there is a corruption, it might not be in the folder you need.

You still need to fix any corruption if it exists, but that is a SharePoint admin issue.

[kkr_nok]

A few more details on what I have tried.

I am just trying to get data from a Sharepoint source at this point.

I need to find a particular folder in Sharepoint, which works fine.  Next, I need to sort the files descending based on Date Created so that I can use the most recent file.

If my M code looks like this:

let
Source = SharePoint.Files("https://Company.sharepoint.com/sites/ABC/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Folder Name")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}})
in
#"Sorted Rows"

I can see three files, which is expected, but one of the files shows an error in the Date Created column.  This breaks the Sort step.  I get an error message with a Data Format Error.

If I use this as M code:

let
Source = SharePoint.Files("https://Company.sharepoint.com/sites/ABC/", [ApiVersion = 14]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Folder Name")),
 #"Removed Errors" = Table.RemoveRowsWithErrors(#"Filtered Rows", {"Content"}),
#"Sorted Rows" = Table.Sort(#"Removed Errors",{{"Date created", Order.Descending}})
in
#"Sorted Rows"

OR this

let
Source = SharePoint.Files("https://Company.sharepoint.com/sites/ABC/"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Folder Name")),
 #"Removed Errors" = Table.RemoveRowsWithErrors(#"Filtered Rows", {"Content"}),
#"Sorted Rows" = Table.Sort(#"Removed Errors",{{"Date created", Order.Descending}})
in
#"Sorted Rows"

then I see the three files with correct date formats in all fields BUT there is an additional row returned with Error in all fields.  Again, when I try to sort by Date Created, the errored row breaks the query.  In these cases, I do get a different error message, which is:

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error. (An error occurred while processing this request.)

Details:

    DataSourceKind=SharePoint

    DataSourcePath=https://Company.sharepoint.com/sites/ABC/_vti_bin/ListData.svc/SolutionGallery

    SPRequestGuid=3c36729f-506a-a000-518d-4926b1c5aa1c

    Url=https://Company.sharepoint.com/sites/ABC/_vti_bin/ListData.svc/SolutionGallery

[edhans]

You should stick with the API= link you get on the first connection. PQ talks to the server to know what to use. I used to hack around that but it wound up causing more trouble than not. In fact, I had one Power BI report that failed and would not refresh again. What happened is I was using 12 and it was originally 13 or something, but 12 got me waht I wanted. Then the SP team at MS made a change to SP online that was totally fine for the 13 code, but as I had overwritten it to 12, it broke my report as unexpected info came through. That was fun to fix.

So, if you are seeing errors in the dates created with the default connection, two things I'd try:

1. Download the files to your HD, delete them in Sharepoint, and reupload. It will change the date of course, but it should be a legit date, not "error."
2. If you don't want to do that, or cannot do it (permissions, company policy, etc.) then either contact your SP admin or open a ticket with MS at the bottom of the support page. The will get some SP Online team members to assist. You will need your Office 365 SP admin or global admin involved as they may need to access the SP admin center in the Office 365 admin tool.

[kkr_nok]

I need to be able to manipulate the file using Power Query and the error line eventually causes the Power Query to break.  I have full access and permissions on the Sharepoint site.  Thanks!

[lbendlin]

Can you add a transformation step "Remove Rows...Remove Errors"?