Table field that contains a formula not able to be "number" in PowerPivot

[zacksg1]

I have a table that includes a few fields which are based on excel formulas. These formulas are in the excel sheet directly, they are not calculated fields in PowerPivot. The formulas return a number (or a blank) and these columns are type "number" in the excel table. Once I pull these into PowerPivot, these columns become type "text." When creating pivot tables, this prevents me from summarizing fields by average, for example, since the values are not technically numbers. I get an error when trying to convert these fields to "number" within PowerPivot.

I imagine that I can copy these fields and paste by value and then set these new fields to be type "number," but I'd much rather just have this work with the original fields!

Thoughts?

 

[zacksg1]

Actually, even copying and pasting by value to create a new field doesn't work when imported into PowerPivot. Even when the field is listed as type "number" in excel, it imports into PowerPivot as "text" and throws an error: The following system error occurred: Type mismatch. Datatype conversion failed for [Table: 'StudentSubject', Column: 'FallToWinterPercentilePointGrowthGOALVALUES', Value: ''].

Could this be something strange happening with the blank cells? It looks like there is nothing between two single quotes for "value" in that error. Other fields that aren't the result of a formula are importing just fine, even with blanks...

 

[RoryA]

The formulas return a number (or a blank)

I suspect the part in bold is the problem - how are you returning a blank?

 

[zacksg1]

The blanks are due to the false condition of if statements of the form =if(SomeCondition, SomeCell-SomeOtherCell,"").

In PowerPivot, if I do isblank() on these cells that look entirely empty, isblank() returns FALSE.

Is there a better way to create blanks?

 

[RoryA]

That's odd - PP converts that sort of field to numbers just fine for me. Which version are you using (both PP and Office)?

 

[zacksg1]

Excel 2013 (64-bit).

I'm not sure how to figure out which version of PowerPivot I have...

 

[scottsen]

Wonder if this is just weirdness between BLANK() vs "" (empty string).

Maybe you can create a calc column in power pivot based on the troublesome column... wrap an IFERROR and some DECIMAL() or ROUND() as appopriate?

 

[zacksg1]

Yes, as a workaround, I was able to create calculated fields in PowerPivot of the form =if([fieldname]="",BLANK(),[fieldname]) which replaced the "" with a BLANK. This works, but it seems like there should be a better way than having to do this for every affected field!

 

[scottsen]

When i try this with cells that I haven't touched, but import into power pivot.... it works fine. They are true blanks and that works out. If I do something like ="" as a formula in an excel cell to make an empty string... indeed I can't convert that to a number.