Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
dovrosenberg
Regular 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
v-juanli-msft
Community Support
Community Support

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

 

 

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.