Forum Discussion
Sharepoint file as data source results in error row that cannot be removed
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?
Hi. Thanks for the reply - I tried several different attempts at removing rows. Removing errored rows, bottom row, etc
- edhans6 years agoCommunity Champion
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_nok6 years agoFrequent Visitor
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
- edhans6 years agoCommunity Champion
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:
- 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."
- 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.