Datatype conversion failed for measure in PowerPivot workbook

Question

I have a problem, during importing data from cube using PowerPivot for Excel. When I import measures to the PowerPivot workbook, it treats numbers as text. When I try to change datatype to decimal number, I get following error:

The following system error occurred: Type mismatch. PowerPivot database error: Datatype conversion failed for [Table: 'Query', Column: '', Value: '6198.9357860000009'].

I'm trying to create pivot table with these values, but while it treats numbers as text, I'm not able to create it correctly. Does anyone know how to deal with it ?

I will be grateful for any help.

Regards, Konrad

Answer 1

I couldn't handle this problem, but I found a workaround. In PowerPivot window, I added new column and inserted following function: =VALUE(SUBSTITUTE(Query[MyMeasure],".",",",1)) It replaces "." with "," and treats values as decimal numbers. Then I hid useless column (with text values) and renamed new column with hidden column name.

Answer 2

Finally I figured out, what was the issue. Regional language was set as Polish and default decimal symbol in Polish language was . instead of ,.

I changed regional langauge to english U.S., reponed excel file and now conversion to decimal number is possible.

Answer 3

This may happen when we have NaN values in measures. Divide by zero errors will throw up exactly the same message in PowerPivot.