Forum Discussion
Problem with auto detected data types from Excel file
Thnaks for you tipps!!
I have checked your points. ...and in the end found out what causes my problem:
1. The whole column hast been formatted as "Bookkeeping" (? "Buchhaltung" in German).
2. In this case, Power Query obviously does only check the first 8 rows if there is a text.
a) if it does find a text: the column gets the type Text
b) if it does not finds a text: the column gets type number and all the others texts in the column get ignored.
All this happens without any type conversion step.
When I format the column as "Standard" and re-format the cells according to what I need ("Currency"), then everything works as exptected.
Isn't that weird?
- Andreas
Yes, it's weird indeed.
I guess the English translation for "Buchhaltung" would be "Accounting", i.e. the number formats that gets all the currency signs left aligned in the cells?
I tried to reconstruct your issue, but no success: data type is based on the first 200 rows. If there is only text in the first 200 rows, it becomes text in Power Query, otherwise any (mixed data), Int64.Type (for whole numbers) or number (for numbers with decimals).
You might consider raising an issue with Microsoft.