I am having some trouble with data types from importes Excel files.
In some columns, the top 15 lines are blank. This leads Power Query to assume decimal as data type for this column - even though there is a text in row 11:
When I put a text, e.g. in row 8, PQ will recognize it as text column:
The only steps I have are Source and Navigation - no data type changes.
Do you have any ideas?
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:
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:
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?
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.
I am having a similar issue. Using BPI desktop, when I import an excel "Table", not sheet, I lose the currency formatting and the cells are changed to decimal. Becasue PBI only lets you format one column at a time it would be a nightmare to approach it from that angle. Is there a setting I can look at so I do not lose my excel formatting during import?