Forum Discussion

lndnbrg's avatar
lndnbrg
Resolver III
9 years ago

Problem with auto detected data types from Excel file

Hi!   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 - ev...
Need Help
lndnbrg's avatar
lndnbrg
Resolver III
9 years ago

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:

 

 

 

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

The general rules are:

  1. 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).
  2. 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:

  1. Are you importing in Power Qury in Power BI Desktop or in Excel?
  2. Are you importing a sheet or a table? Tables are prefered.
  3. Can you share some details how your data looks like in steps Quelle / Navigation?
  4. Do you get any error messages?

 

 

 

  • lndnbrg's avatar
    lndnbrg
    Resolver III
    9 years ago

    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

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      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.