Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
I know this an old post, I have the same issue, did you ever get a solution to this?
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.
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingA 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
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:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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!
Can you add a transformation step "Remove Rows...Remove Errors"?