Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lndnbrg
Resolver III
Resolver III

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
sverderame
Helper I
Helper I

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

MarcelBeug
Community Champion
Community Champion

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)

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

 

 

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)

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.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.