Power query - xls import error DataFormat.Error: External table is not in the expected format.

[ceckin]

Hi

i have searched for an answer high and low but unfortunately i am stuck. I have files coming out from SAP in XLS format. Up until last week i had zero issues using power query to merge all files from a folder.
Step 1: Specify Folder
Step 2: Filter only xls files
Step 3: Power query gives a warning that it does not like them and gives me the option to choose
Step 4: i choose CSV
and the world was fine.

Today when i try to merge i get DataFormat.Error: External table is not in the expected format.
After that i have not option to continue.

I have seen that i could be due to different versions of Power BI and Office (32 bit vs 64 bit). So i fixed that but it still remains.

If anyone can suggest something it would be amazing as i have been reading forums for couple of hours now and no luck

 

[HeatherDavis]

Hi @ceckin - I wanted to share a way to make this work without the extra step of saving as CSV and/or open and save as XLSX. This may not be a blanket resolution to opening any xls file that throws the error, but I noticed when working with these files from SAP, they were opening as a Web Page type in Excel.

Pros: No opening files and resaving or editing to change file extension. Just export and refresh your workbook(s).
Cons: Does not work if xls files are on SharePoint. Column headers are missing, so you must rename Column1, Column2, etc.

//Data
let
    Source = Folder.Files("[YourFullPathToFolderHere]"),
    FilterHidden = Table.SelectRows(Source, each ([Attributes]?[Hidden]? <> true) and ([Attributes]?[System]? <> true)),
    AddAsWebpage = Table.AddColumn(FilterHidden, "FileContent", each Web.Page([Content])),
    ExpandData = Table.ExpandTableColumn(AddAsWebpage, "FileContent", {"Source", "Data"}, {"Source", "Data"}),
    FilterTableData = Table.SelectRows(ExpandData, each ([Source] = "Table")),
    DemoteHeaders = Table.AddColumn(FilterTableData, "CorrectedData", each Table.DemoteHeaders([Data])),
    SelectCorrectedData = Table.SelectColumns(DemoteHeaders,{"CorrectedData"}),
    ExpandCorrectedData = Table.ExpandTableColumn(SelectCorrectedData, "CorrectedData", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"})
in
    ExpandCorrectedData

That should bring your xls file data in. From there, either rename your columns or use a table in the Excel file to map your header