Sharepoint files list adds error row that I can't remove

[rbbi]

hi,

I'm getting a list of files from OneDrive - i.e. Sharepoint - for later processing.
The list is OK mostly, and I filter it for path and file extension, but there's an error row being added that I can't remove using the normal error filtering.
I can filter the top 7 rows OK, but I won't know how many files there'll be so this isn't a solution.

Here's the M ...
Notes: the source is at the top level of the sharepoint site for ANOTHER USER in the domain who has given me read access. Starting at the top level is required ... you can't specify a subfolder. The idea is to filter the resulting list of files for the path, name pattern to get the files I need, BUT the list has this error row added and the final RemoveRowsWithErrors step has no effect and prevents me from processing the files

let
// OneDrive ...
Source = SharePoint.Files("https://ZZZZZZZZZ.sharepoint.com/personal/USER_DOMAIN_com", [ApiVersion = 14]),
#"Filter path" = Table.SelectRows(Source, each Text.EndsWith(Text.Upper([Folder Path]), "/XXX REPORTING/SOURCE - QB REPORTS/") or Text.EndsWith(Text.Upper([Folder Path]), "\XXX REPORTING\SOURCE - QB REPORTS\")),
#"Filter type" = Table.SelectRows(#"Filter path", each Type.Is(Value.Type([Name]), type text) ),
#"Filter file type" = Table.SelectRows(#"Filter type", each Text.StartsWith([Name], "XXX EXPENSES ") and Text.EndsWith([Name], ".xlsx") ),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Filter file type", {"Content"})
in
#"Removed Errors"

If I click on one of the error cells I get this ..
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://ZZZZZZZZZ.sharepoint.com/personal/USER_DOMAIN_com/_vti_bin/ListData.svc/SolutionGallery
SPRequestGuid=46cc919e-70ee-6000-bec7-b0732bdbec71
Url=https://ZZZZZZZZZ.sharepoint.com/personal/USER_DOMAIN_com/_vti_bin/ListData.svc/SolutionGallery

[Pascal_KTeam]

I'm facing the same issue. My user has full access to the SharePoint site so refreshing works fine. For users that only have access to subfolders, refresh doesn't work and removing errors doesn't do anything. Hopefully, somebody has found a solution in the meantime.

[Timbollew]

I'm also plagued by this issue, I did find another post with the same issue and for me selecting keep top rows allows me to filter out the error, but doesn't work for my use case as I want to only show the most recent file and so the number of rows changes.

[trebgatte]

I just created this blog post on merging Excel files for another situation. Have a look as it may point out some potential alternatives for this issue. https://marqueeinsights.com/how-to-merge-multiple-excel-files-with-power-bi/

Hope this helps.

--Treb, Power BI MVP

[rbbi]

Hi, thanks but if you read carefully what I posted, the problem I'm having is using Sharepoint.Contents in M to list the files *under another user's account* .... and getting some kind of permissions error that *can't be filtered out* using the normal "remove errors" step. This non-removable error prevents me from going any further to filter the list and combine files like your video shows.

[trebgatte]

I suspect this is a security trimming behavior issue in SharePoint API, not Power BI itself. All access is site specific, so if you have a site and a subsite, that's two different queries and the account with data access has to have Reader rights in SharePoint to access the content within each site. If you are trying to do this over the old My Sites, that's likely a big question mark since I don't know anyone still using My Sites personally.

[v-yuezhe-msft]

@rbbi,

I am unable to reproduce this issue in Power BI Desktop September 2018 version.

In your scenario, please update your Power BI Desktop to latest version, clear permission of the current data source and re-enter your credential. Make sure you use organizational account authentication.


If you still get error row, please remove bottom row using option below.



Regards,
Lydia

[rbbi]

Hi Lydia @v-yuezhe-msft,

thanks for looking at this for me.

I'm using the September release ...

Release: September 2018

Product Version: 2.62.5222.601 (18.09) (x64)

OS Version: Microsoft Windows NT 10.0.17134.0 (x64 en-US)

If I try Remove Botttom Rows (1) I get an error and no rows at all.

I think a contributing factor is that the files are in another user's folder (subfolder under his home folder).

So he has given me permission to read that subfolder but not other files/folders in his area. But because I have to request the list of files from the TOP (home) level then filter by folder name, I think the error might be coming when the query hits files I don't have permissions for. Is it possible for you to try this scenario?

many thanks, Rod

[v-yuezhe-msft]

@rbbi,

I test the scenario as yours, and I find the issue is caused by that you use ApiVersion = 14 in source line, please change it to 15. and you will not get errors.

Source = SharePoint.Files("https://ZZZZZZZZZ.sharepoint.com/personal/USER_DOMAIN_com", [ApiVersion = 15])

Regards,
Lydia

[rbbi]

thanks @v-yuezhe-msft, but if I change it to ApiVersion=15 I get an error and no rows returned.

Here is the error ...

[v-yuezhe-msft]

@rbbi,

Do you use the SharePoint Folder connector in Power BI Desktop to connect to the data source? Based on my test, when using this built-in connector, the ApiVersion is 15 by default.

Also do you use the URL provided by the folder owner? The URL contains the account name of t he folder owner but not yours.

Regards,
Lydia

[rbbi]

hi @v-yuezhe-msft,

yes, I'm using the Sharepoint Folder connector. in M this becomes SharePoint.Files

If the URL is MY username's home folder

https://DOMAIN-my.sharepoint.com/personal/info_DOMAIN_com/

then I get a file list regardles of ApiVersion being 14 or 15 and no error

But I really need to get the files from the OTHER user's folder

https://DOMAIN-my.sharepoint.com/personal/OTHERUSER_DOMAIN_com/

If I use ApiVersion=15 then I get an error and no file list

If I use ApiVersion=14 then I get a file list but with an added error row that I can't remove, and this prevents me from using the file list if I try to APPLY the datasource. These findings are shown in the M code below ... where I uncomment one of the Source= rows to test

Note that though I am using the Septemeber 2018 release, my Windows system prompts me to download a "new" (?) version of Power BI !!!

thanks again for looking into this for me.

let
// MYUSER (info) with ApiVersion=15 OR 14 returns rows for all MY xls files with no error
//Source = SharePoint.Files("https://DOMAIN-my.sharepoint.com/personal/info_DOMAIN_com/", [ApiVersion = 15]),
//Source = SharePoint.Files("https://DOMAIN-my.sharepoint.com/personal/info_DOMAIN_com/", [ApiVersion = 14]),

// OTHERUSER with ApiVersion=15 gives error and no rows returned
//Source = SharePoint.Files("https://DOMAIN-my.sharepoint.com/personal/OTHERUSER_DOMAIN_com/", [ApiVersion = 15]),

//DataSource.Error: Web.Contents failed to get contents from
// 'https://DOMAIN-my.sharepoint.com/personal/OTHERUSER_DOMAIN_com/_api/web/lists/getbyid('d9aa9b63-e289...'
// (500): Internal Server Error
//Details:
// DataSourceKind=SharePoint
// DataSourcePath=https://DOMAIN-my.sharepoint.com/personal/OTHERUSER_DOMAIN_com
// Url=https://DOMAIN-my.sharepoint.com/personal/OTHERUSER_DOMAIN_com/_api/web/lists/getbyid('d9aa9b63-e289...

// OTHERUSER with ApiVersion=14 returns rows for all OTHER users xls files but adds error row at end that can't be removed
Source = SharePoint.Files("https://DOMAIN-my.sharepoint.com/personal/OTHERUSER_DOMAIN_com/", [ApiVersion = 14]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], ".xls"))
in
#"Filtered Rows"

[v-yuezhe-msft]

@rbbi,

I test the scenario as yours, but I am unable to reproduce it. How about the owner grants your root folder permission in OneDrive?

Regards,
Lydia

[pvcleave]

Has a solution ever been found for this. I have the exact same issue and cannot figure it out.

[rbbi]

no solution for me. I had to get the files moved to a place I could read them under my folder structure.

[Anonymous]

Is any solution found yet?  I still face this problem. 

[rbbi]

Hi, sorry no fix found yet. I load files individually as a workaround.

[Anonymous]