What are the characteristics of an excel that Power BI does not support?

[vitexo87]

I have an excel spreadsheet (.xlsx) that cites multiple columns, some of which contain calculated data, others contain special characters (&,%, $, # etc ...), have date columns that are displaying data in Integer, for example 451287 when it should have 01/01/2017, and has columns of values R $ that follow the numerical pattern of 1,000.00 but there is a line or another that the value presented is in the format R $ 1,000.00.

When I load this spreadsheet in Power BI, several values have an error, what are the characters that are not supported when loading data into Power BI? Is there any way to dumbify Power BI to fix this error automatically by applying some conversion?

Thank you so much

[v-jiascu-msft]

Hi @vitexo87,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[v-jiascu-msft]

Hi @vitexo87,

That's a little strange. The error message showed the file can't be found. 

About the characters, I think it's the data type matters, not the characters themselves. The data type of column "data realizada" is decimal. So no chars are accepted. Please check it out.

Best Regards!

Dale

[MarcelBeug · Accepted solution]

In general, when importing data from Excel into Power Query, a step #"Changed Type" is generated that transforms column data types. This step should always be verified and adjusted if so required, so the data types fit the actual data.

Typically the data type is determined based on the first 200 rows, so if you would have whole numbers in a column, but decimals after row 200, then the data type will be set to Int64.Type (whole number) and errors will show up when loading the data into the datamodel.

Within the Power Query environment, it is no error if a value in a column doesn't fit the data type of that column.

You might also consider removing the #"Changed Type" all together, so each column will be typed as any, but that may prevent correct modelling in the data model.