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.
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.
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
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.
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.
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
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])
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.
yes, I'm using the Sharepoint Folder connector. in M this becomes SharePoint.Files
If the URL is MY username's home folder
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
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.
// 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
// (500): Internal Server Error
// 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"))
I test the scenario as yours, but I am unable to reproduce it. How about the owner grants your root folder permission in OneDrive?