An error occurred in the ‘Transform File’ query. DataFormat.Error: File contains corrupted data. Det

[Sundetkhan]

Dear All, 

I have transformed (loaded & combined) from SharePoint Library more than 800 Excel files but faced an error 

I understood the problem with the Source file but There is no indication of the error to the file. How can I quickly find which exactly from the 800 excel file needs to be corrected? 

 

[Sundetkhan]

let
Source = SharePoint.Files("https://nssltdkz.sharepoint.com/sites/RozhkovskoyeFieldDevelopmentProject/", [ApiVersion = 15]),
#"Filtered Rows-1" = Table.SelectRows(Source, each Text.Contains([Folder Path], "RozhkovskoyeFieldDevelopmentProject/Transmittals")),
#"Filtered Rows-2" = Table.SelectRows(#"Filtered Rows-1", each not Text.Contains([Folder Path], "%")),
#"Filtered Rows-3" = Table.SelectRows(#"Filtered Rows-2", each not Text.Contains([Folder Path], "#")),
#"Filtered Rows-4" = Table.SelectRows(#"Filtered Rows-3", each Text.Contains([Name], "RZK-NSS-UOG-DTR")),
#"Filtered Rows-5" = Table.SelectRows(#"Filtered Rows-4", each ([Extension] = ".xlsx")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows-5", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Column1", "Column10", "Column11", "Column12"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column2] <> null and [Column2] <> "Annex I - Technical specifications" and [Column2] <> "Annex II - SOW" and [Column2] <> "Annex III - Feed documentation" and [Column2] <> "Annex IV - Initial data" and [Column2] <> "Attachment - 4" and [Column2] <> "Attachment -1" and [Column2] <> "Attachment -2" and [Column2] <> "Attachment -3" and [Column2] <> "Preliminary Vendor list" and [Column2] <> "Номер документа / #(lf)Document Number")),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Source.Name"}, #"Filtered Rows-5", {"Name"}, "Filtered Rows", JoinKind.LeftOuter),
#"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Date created", "Folder Path"}, {"Date created", "Folder Path"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Filtered Rows",{{"Date created", type date}})
in
#"Changed Type"

[Sundetkhan]

FYI. It is Electronic Document Management Systems for Document control, we were transmitting a lot of docment to Client and on a daily basis this excel files has been creating and uploading to sharepoint. All Excel files that I wanted to combine had an identical table. The table was just showing what document has been transmitted to the Client. So I wanted to make list of all transmitted document to client (like history to see the overall picture. The error occurred when I combined binary excel files and invoked by function data inside of each excel. Some excel failed to invoke and I couldn't find which one it was, it was just saying Dataformat error. I was wondering if there a quick way to find the damaged or error file.

[v-stephen-msft]

Hi @Sundetkhan ,

One possible reason is that the file is password protected. If that, you may need to remove the file or reconnect them where they don't need password protection.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[JamesRobson]

Can you get a compelte list of source files?

If you can then drop that into a table and extract the File name list containing the error and do a Full Anti join to show the missing line....

https://www.youtube.com/watch?v=vImf2enzZVU

Thanks,

[Sundetkhan]

I was sitting and looking to each Excel file and finally found one which was damaged and had a $sign in the name. It takes a lot of time to search manually. I was thinking if there is a way to find that damaged file quicker. I didn't get what you mean by dropping it into the table. The error occurred when I combined binary excel files and invoked by function data inside of each excel. Some excel failed to invoke and I couldn't find which one it was, it was just saying Dataformat error 

[Anonymous]

are you able to copy and paste your queries from advanced editor to here?