DataFormat.Error: External table is not in the expected format. Details: when i've removed rows and

[shneierl]

I am trying to combine files that are in one of our shared folders ( network drive not sharepoint) so i go into get data from folder navigate to the root folder in question and it transform dasta. this all seems to go through fine. i then selected a random file that i knew no one was using at the time (as the first file in the folder was in use by someone else) and asked it to combine for the data in within sheet4 (name not important) this seemed to work other than this sheet having a few rows with merged headers before the main header row so I needed to change what the promoted header row was. 

Itried doing remove top rows then promoting from within the merged query but that kept coming to an error as it was only removing the top rows from the first file not all 99 files in the folder. after a bit of gogling I realised I needed to do the remove rows step within the sample query transform query rather than in the output as i'm used to (don't normally do complex PQ)

So I went back to this query deleted the stages it had added after navigation (the automatic promotion of headings. then I removed the top rows that I knew were just where my merged cell headings were and then hit use first row as headers. However the file now generates the following error only after the promotion (was in if i don't promote the heading). 

DataFormat.Error: External table is not in the expected format. the details just provide the file name of the file i used for my sample file. i can't think of anything wrong with the file as it is built in excel and is rather simple design other than having merged cells which make it not a simple merge task.

Any idea why this error would come up only if i use the promote headers step after deleting the top rows which are in the format of the table below (example given to show why they aren't useful headings)

main group 1				main group 2
sub group 1		sub group 2		sub group1		sub group 2

I am not sure how i can workaround this other than copying and pasting all the data into a single sheet which is unfeasible and removes the purpose of the query, or delete the column i would normally use as the row headers from the sample file then rename all the columns to what they should be called in a subsequent step. If anyone has a better solution I'm open to ideas.

[v-jingzhang]

Hi @shneierl 

I cannot reproduce this error with the sample data. Can you provide a sample file (removing sensitive data) which can help reproduce this error?

Can you test if you don't use Folder connector, connect to the sample file with Excel connector and perform the same operations, will this error happen?

I found some solutions for the similar question. Try if they can work. It seems this error is not caused by the table structure but caused by something outside. 

https://community.powerbi.com/t5/Desktop/External-table-is-not-in-the-expected-format/td-p/153986 

https://www.mrexcel.com/board/threads/power-query-xls-import-error-dataformat-error-external-table-i... 

https://social.technet.microsoft.com/Forums/lync/en-US/df02e679-a65e-4d9c-90e5-05074770dfa7/dataform... 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[shneierl · Accepted solution]

Thanks for coming back to me. I appreciate that replicating this issue is probably hard trying to recreate sample data based on a folder structure might be hard but I could try and build a simple table for what the sample file should look like and link that if useful. 

However after some brute force debugging (just repeating steps one at a until it worked) and I think I have Iresolved the issue,. basically I think for some reason i was hitting a time out error without it being identified as i hit refresh preview or split the remove top rows into a couple steps and it seemed to eventually work without the error coming through. my other guess is someone in a different team was working on the file i was using as the reference file in my source data even though i don't think that was the case. i'd still love to know what caused the error but as i have been able to pull together a consolidated version of the tab i was testing now i just need to replicate it for all the tabs in this workbook