HOW TO TURN NEGATIVE VALUES IN A COLUMN TO ZERO

[TOYER01]

I have a column with some values containing Negatives and "NAH"

How can i turn those negative numbers and "nah to 0

that is, anytime the result in this column show a negative value or "nah" ti should automatically convert it to 0

[Ahmedx]

you have to do it in power query and for that you need to click

[Ahmedx]

pls try this

if 
Number.IsNaN([Sales]) or [Sales]<0
      then 0 else [Sales]

 

[TOYER01]

It didnt work for me.

I have a table with 5 columns spooled from a source. So i added "sales" Column to the table.  

Sales Column is the result of the calculation of ColumnA divided by 2

So what I want is, how to turn "Nah" and all the negative numbers(See Screenshot below) to Zero (0)

[Ashish_Mathur]

Hi,

Assuming the Sales column was created in the Query Editor AND you want to get the desired result in the Query Editor itself, write this Custom column formula in the Query Editor

=if [Sales]="NaH" or [Sales]<0 then 0 else [Sales]

If you want to get the resutl in the Data Model, then write this calculated column formula

=if(or(Data[Sales]="Nah",Data[Sales]=0),0,Data[Sales])

Hope this helps.

[TOYER01]

thank you @Ashish_Mathur 

i got this error

[Ashish_Mathur]

Hi,

Does this calculated column formula work?

if(or(Data[Sales]="Nah",Data[Sales]="0"),0,Data[Sales])

[TOYER01]

@Ashish_Mathur , this is the error too

[Ashish_Mathur]

Hi,

Share the download link of the PBI file with the formula already written (the one that yields an error).

[TOYER01]

thanks @Ahmedx ,  I cant see Custom Column

I clicked new column, and enter the code above, gives error