cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dovrosenberg Frequent Visitor
Frequent Visitor

Error trying to open Sharepoint Excel file ("corrupted data") - but only for one account

I am trying to load an Excel file from OneDrive/Sharepoint.  I had it working perfectly but then attempted to move it from one user account (for testing) to another (for production) and am now getting an error.

 

Here is the M code:

 

 

let
   Filename="Material Master.xlsx",
   Sheetname = "Sheet1",
   ONEDRIVE_PATH = https://company-my.sharepoint.com/personal/username1_company_com/",   // anonymized
DATA_FOLDER = "testdata",
Source = SharePoint.Tables(ONEDRIVE_PATH, [ApiVersion = 15]), DocumentsFolder = Source{[Title="Documenti"]}[Items], #"Renamed Columns" = Table.RenameColumns(DocumentsFolder,{{"ID", "ID.1"}}), #"Expanded File" = Table.ExpandRecordColumn(#"Renamed Columns", "File", {"LinkingUrl", "Name"}, {"File.LinkingUrl", "File.Name"}), #"Filtered Folder" = Table.SelectRows(#"Expanded File", each (Text.BeforeDelimiter(Text.AfterDelimiter([File.LinkingUrl], ONEDRIVE_PATH & "Documents/"),"/") = DATA_FOLDER)), #"Filtered File" = Table.SelectRows(#"Filtered Folder", each ([File.Name]=Filename)), #"Added Temp" = Table.AddColumn(#"Filtered File", "Temp", each Web.Contents(Text.BeforeDelimiter([File.LinkingUrl],"?"),[ManualStatusHandling={400..499}])), #"Added FileContent" = Table.AddColumn(#"Added Temp", "FileContent", each Excel.Workbook([Temp],null,true)), FileContent = #"Added FileContent"{0}[FileContent], SheetContent = FileContent{[Item=Sheetname,Kind="Sheet"]}[Data] in SheetContent

This works perfectly for one user.

 

When I change ONEDRIVE_PATH to use a different user, it gets through the #"Added Temp" step just fine.  But the Excel.Workbook call returns 

 

 

DataFormat.Error: File contains corrupted data.
Details:
   Binary

 

To me this suggests that it's not a security issue (it can find the folder and see the file).  I can download the file using the LinkedURL and Excel opens it just fine.  I have downloaded the file from the other account and uploaded to this one to make sure it's the same file.  And it's not just this file - it appears to be any file I attempt to open this way from this account.

 

Is there some other setting at the account level that could be doing it?  The only difference I can think of is that the problem account is shared among multiple users.  Several people can access the account's email and send on it's behalf.  I'm not sure how this could be related, but raising it as I don't see any other differences.

 

Thanks!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Error trying to open Sharepoint Excel file ("corrupted data") - but only for one accou

Hi @dovrosenberg 

Are the credentials for two Sharepoint URL the same?

Are the tables and columns from URL 1 the same with those from URL 2?

Have you ever use any custom visuals on the report page like Map visual?

 

Best Regards
Maggie

 

 

dovrosenberg Frequent Visitor
Frequent Visitor

Re: Error trying to open Sharepoint Excel file ("corrupted data") - but only for one accou

The credentials are different, but that part appears to be working because it can get the folder listing just fine.  The tables and columns are the same.  No custom visuals.