Error with automatic columns detection when combining excel files in folder

[Usix]

Hi everyone, I was starting to load & combine all files in a folder,  I did the first steps : my files contains data which starts only at column C so I add a step to delete the first 2 columns in PQ, then first lines and promote headers. I check if there are errors by displaying the "columns quality " and make it control the entire file instead of the 1000 first lines, everything is ok. I apply and load and then I get the error "oledb or odbc column xxx missing. So back to PQ and attempted to change the exemple file to see how it loads everyone of them, the first 2 files loads with 2 empty columns at the begining, I jump to the last one : PQ loads it with only 1 empty column at the begining ! This is why my steps leads to errors ..I don't understand why this behavior ... anyone have an ideas on why and how to fix this ? meanwhile I will transform the source files but I think it's weird ... 

Thanks in advance

[Usix · Accepted solution]

Thank you for your quick response, yes there is a "changed type" the problem is that PQ doesn't seem to detect the same thing with the first 7 files & the 3 others (there are 10 files in the folder) but they look exactly the same ...  1 header in B2 (merged cells), column headers starting at C4 and data starting at C5; and objectively it should be the same as this is the same extract from the same tool, only for different months. 

I just tested and when I remove the file 8, 9 & 10 it works like a charm.

Steps of the "transform sample file" query :

- Source

- Navigation

- Removed columns

- Deleted first lines

- Headers promoted

- Changed type

EDIT : It seems that PowerBI doesn't like much when a merged cell begins before datas column ... I changed a bit the tool to align the report title with datas so all start in column B : report title, data headers, datas, and it works.

[amitchandak]

@Usix ,  If you removed columns before promoting the header and the Promote Headers step was added automatically, please check whether a Changed Type step was also added automatically and still contains references to the columns you removed.

Please share the Power Query steps as well.