# Convert column value if it is a number and text as zero



## spalmer (Jun 23, 2013)

Hi,

this should be simple but I think I'm over thinking this! I have a column filled with numbers and text. I need to convert the numbers to numbers and the text to 0. I'm doing this in powerpivot. I'm getting as far as a True or False but can't actually get the data to return properly.

The following formula is in the convert to number column. The table below shows the original data and what I want as the end result.
=IF(ISERROR(VALUE([Project Area  New USF])),0,VALUE([Project Area  New USF]))


original data imported as text
end result should be...
Project Area  New USF
convert to number
3600
3600
NA
4500
4500


<tbody>

</tbody>
Thanks for any help!

regards, Suzi


----------



## Scuderia (Jun 24, 2013)

Hi

I think that this formula should cover what you require...

=IF(ISERROR(IF(ISTEXT([PROJECT AREA NEW USF]),VALUE([PROJECT AREA NEW USF]),IF(ISNUMBER([PROJECT AREA NEW USF]),[PROJECT AREA NEW USF],0))),0,IF(ISTEXT([PROJECT AREA NEW USF]),VALUE([PROJECT AREA NEW USF]),IF(ISNUMBER([PROJECT AREA NEW USF]),[PROJECT AREA NEW USF],0)))


----------



## spalmer (Jun 24, 2013)

Hi Scuderia,

Thanks for the formula. When I entered the formula, I received the following error:"The second and third arguments of function IF have different data types. This is not supported."​
I'm assuming this may be a DAX thing. Here's a copy of the exact formula I entered.
=IF(ISERROR(IF(ISTEXT(ProjectProperties[Project Area  New USF]),VALUE(ProjectProperties[Project Area  New USF]),IF(ISNUMBER(ProjectProperties[Project Area  New USF]),ProjectProperties[Project Area  New USF],0))),0,IF(ISTEXT(ProjectProperties[Project Area  New USF]),IF(ISNUMBER(ProjectProperties[Project Area  New USF]),ProjectProperties[Project Area  New USF],0)))​
I'm also unsure if I made it clear in my original example where I would like to see a 0 in place of the text.
Thanks!




Scuderia said:


> Hi
> 
> I think that this formula should cover what you require...
> 
> =IF(ISERROR(IF(ISTEXT([PROJECT AREA NEW USF]),VALUE([PROJECT AREA NEW USF]),IF(ISNUMBER([PROJECT AREA NEW USF]),[PROJECT AREA NEW USF],0))),0,IF(ISTEXT([PROJECT AREA NEW USF]),VALUE([PROJECT AREA NEW USF]),IF(ISNUMBER([PROJECT AREA NEW USF]),[PROJECT AREA NEW USF],0)))


----------



## Scuderia (Jun 24, 2013)

Hi

Sorry about this however I think I've misunderstood. I thought your formula was to be applied to an excel import using standard Excel formulae prior to anything dax related to do with power pivot.
Sorry again as I do not know anything about power pivot.


----------



## spalmer (Jun 24, 2013)

No problem! It was worth a shot though. Hopefully there's someone else with the PowerPivot experence to help.


----------



## masplin (Jun 29, 2013)

What error do you get with your formula?  I spent quite bit of time understanding how to work out what was a number with powerpivot. 

What i think you need to do is add a zero which forces it to try to calculate it as a number. Something like

IF(
    IFERROR(
                ISNUMBER([P A N USF]+0),
                FALSE
                 ),
     [P A N USF],
      0
  )


----------

