Forum Discussion
Excel Import into Query losing Column Headers
I have automated extracts from multiple different ERP system into Excel, to be subsequently imported into PBI. I have not had an issue other than on two (random) occasions related to one file. The file has imported perfectly without an issue, until for some unknown reason Power BI cannot recognise the column headers in the first row, instead it reports that row as containing no data (null is returned). The Excel file is automatically scripted daily, so no change to format, column headers etc. There is no User intervention in the Desktop file, and it is auto published.
Why is Power BI suddenly unable to find the titles and therefore replacing them with "null"? Is this a bug in Power BI?
6 Replies
Luklaman , I am assuming you have defined step to convert the first row into the header in data transformation/edit query and it is not disturbed
amitchandak Yes. The file has been exported for an ERP system into Excel and imported into PBI for ~45 days without fault, and for no reason at all, PBI this time thinks there is no data in row one of the Excel file, puts 'null' in row one of the query.
I can only assume it is a bug in PBI, given the output to Excel is automated, it has worked without an issue for X days, the column headers still exist in the Excel file, it imports the first row from Excel into PBI, but cannot find any data and therefore puts 'null' into the fields.
HI Luklaman,
AFAIK, power bi does not support advanced format headers(e.g. multiple level header or fields that combine multiple row/columns) Please double-check on your workbook sheets to confirm they are stored as a common data table format.
Regards,Xiaoxin Sheng
Hi Anonymous
The file has been exported from SAP for ~45 days and imported into PBI. The export and import/publishing are automated, and therefore the file format should not change. This is the second time this one file has failed on import for the same issue.
The first time it failed I removed all formatting from the first row in the Excel file, but that did not resolve the issue. There are no merged cells, all the column headers are in row 1. I have managed to resolve the issue manually by copying the data into a new workbook, saving the file over the first one. But I shouldn't need to manually intervene in the process!
This is a strange one......and I can only think Power BI has a bug (how can it fail after X days when the input file format is unchanged?).
HI Luklaman,
What methods did you use to import your records? Get data with excel connector or using import feature to import excel workbook?
Can you please share some dummy data with the minimum structure to test?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hello, how do you check if workbook sheets are stored as common data table format? I've the same issue and can't seem to find this info on WWW.
