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

[spalmer]

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]))

[TABLE="width: 500"]
<tbody>[TR]
[TD]original data imported as text
[/TD]
[TD]end result should be...
[/TD]
[/TR]
[TR]
[TD]Project Area New USF
[/TD]
[TD]convert to number
[/TD]
[/TR]
[TR]
[TD]3600
[/TD]
[TD]3600
[/TD]
[/TR]
[TR]
[TD]NA
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4500
[/TD]
[TD]4500
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any help!

regards, Suzi

 

[Scuderia]

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]

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!

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]

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]

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

 

[masplin]

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
)