Power Pivot currency in a table problem.

[Saud_Jilani]

Hi all,
I have a table in Excel. When I add it to the model, it works properly only if there is no blank row in the table. If there is a blank row, then the currency in the columns of Sale Price (or any other price column) does not show as currency in the data model, nor can it be changed into currency (even though it is a currency in the Excel file). It shows it as text - which does not convert into currency (attached screenshot) which means I cannot use it in any calculations. It works as expected when there is no blank row, but my need is to make it work even when the table has blank rows at the end.

My understanding is that it happens because there is a formula in the blank rows and not in the unempty rows - which causes confusion for the program.

 

[GraH]

Does the formula returns a value like ""? If so, replace it with blank().
"" is a text of zero length, while blank() is empty, the absence of any value.
I could explain why the column is detected as containing text.