Forum Discussion
Error with automatic columns detection when combining excel files in folder
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
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.
2 Replies
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.
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.
