cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lndnbrg Member
Member

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 - even though there is a text in row 11:

2017-04-11 11_42_04-Leistungsstand - Abfrage-Editor.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When I put a text, e.g. in row 8, PQ will recognize it as text column:

 

2017-04-11 11_44_12-Leistungsstand - Abfrage-Editor.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The only steps I have are Source and Navigation - no data type changes.

 

Do you have any ideas?

 

Thank you!!

Andreas

6 REPLIES 6
MarcelBeug Super Contributor
Super Contributor

Re: 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.

Specializing in Power Query Formula Language (M)
Highlighted
lndnbrg Member
Member

Re: Problem with auto detected data types from Excel file

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:

 

2017-04-11 17_43_34-Leistungsstand - Abfrage-Editor.png

 

 

MarcelBeug Super Contributor
Super Contributor

Re: Problem with auto detected data types from Excel file

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?

 

 

 

Specializing in Power Query Formula Language (M)
lndnbrg Member
Member

Re: 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

MarcelBeug Super Contributor
Super Contributor

Re: Problem with auto detected data types from Excel file

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.

Specializing in Power Query Formula Language (M)
sverderame Regular Visitor
Regular Visitor

Re: Problem with auto detected data types from Excel file

Hi 

 

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?

 

Thank you,

Steve

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 98 members 1,513 guests
Please welcome our newest community members: