Forum Discussion
Problem with auto detected data types from Excel file
In your first picture there is no text; -0,002 is a number (these are right aligned in Excel by default).
Otherwise you can always adjust the automatically generated code for the data type changes.
Thanks for having a look at my problem, Marcel!
The text is in Excel right above the -0,002 - you can see a fraction of the text in the upper screenshot.
It seems that Power Query imports a column from Excel as decimal if there is no text in the first 8 rows. By that, all texts below row 8 gets removed... before there is even a step to convert any types.
Is there any way to change this behaviour?
My steps look like this:
The general rules are:
- The auto type detection is based on the first 200 rows and it allows for other types as whole (like whole number, logical, date/time).
- You can switch off auto type detection in Power BI Desktop via options - current file - data load - uncheck auto type detection.
But it seems you have complete other problems as you don't even get to the data type detection?
It sounds strange. Can you provide more specifics:
- Are you importing in Power Qury in Power BI Desktop or in Excel?
- Are you importing a sheet or a table? Tables are prefered.
- Can you share some details how your data looks like in steps Quelle / Navigation?
- Do you get any error messages?
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
