Power Query - Self Referencing for Comments, but getting DataFormat.Error

[MichaelRm]

Hi,

So I recently made a file following the instructions in this Link . Recently, whenever I refresh I always get the error "DataFomat.Error" and after opening the query, the error always shows up after "= Table.ExpandTableColumn" with the error saying "An error occurred in the ‘Transform File’ query. DataFormat.Error: External table is not in the expected format. Details: 4d1403fc-67ac-4180-8cdc-f6d019f3d6a3.XLS" or something similar. Can anyone help solve this?

[#2]

Hi @MichaelRm 

A DataFomat.Error in your scenario might be due to a DataSource file (your 4d1403fc-67ac-4180-8cdc-f6d019f3d6a3.XLS) generated by a system/application other than Excel

A bunch of apps generate incomplete/wrong Excel files. Although they (usually) can be opened with Excel, the Power Query connector for Excel files may fail opening/decoding them

Try to: Open your DataSourceFile in Excel > Save it > Close it > Refresh your query

If you can refresh the query you know where the problem comes from. Otherwise if there's nothing top secret in your DataSourceFile post it their (+ the workbook with your query)

[#3]

Hi @MichaelRm 

Have you made any progress identifying the root cause of the issue?

Thanks