replace numeric values with 0 in Power Query

[nj17]

Hi

@amitchandak  @Ashish_Mathur 

How can I replace below column's numeric value to 0 in Power query

Also I cant see any advanced option to replace all values to 0

Thanks

NJ

[ChillieWillie]

You can use Table.TransformColumns to transform your column. When your want is conditional, you can use "each" to evaluate by each row. The underscore character _ refers to "each cell's value"

Here I am transforming the column using an If statement. IF _ (cell value) does not equal null, then change to zero, else keep _ (cell value).

Here is the M code you can paste in the formula bar and update to your file: 

= Table.TransformColumns(#"Changed Type",{{"Amount", each if _ <> null then 0 else _, Int64.Type}})

For this code to work in your file, "Changed Type" refers to the previous applied steps and "Amount" refers the the column name you are transforming:

For those wanting further illustration: 

Here is an example by using a drop down menu to transform a column:

This is the code it generates:

= Table.TransformColumns(#"Changed Type",{{"Amount", Number.RoundUp, Int64.Type}})

What we are doing in my solution, is changing the inner part of of this generated code to transform by the cell in the column using the keyword "each" then giving it my conditions.

[AliceW]

Thanks, man! Beautiful solution.

[v-yueyunzh-msft · Accepted solution]

Hi, @nj17 

According to your describtion,You want to replace the non-null value of a column with 0. Right?

Here are my steps you can follow:

(1) This is my test data :

(2)You can click “Custom Column” in Power Query Editor and enter as follows:

if [Column1] <> null then 0 else null

(3)Then we just need to delete the original column,then we can meet your need :

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

Try this M code

=if Value.Type([Amount]) = type number then 0 else null

Hope this helps.

[Shreeram04]

Hi @nj17,

If you don't want to do any further calculations using this column means you can convert data type from number to text and you can change it into 0.

Step 1:

Change the value to 0 for two to three value power bi automatically change the value to 0.

Thanks,

[nj17]

Hi @Shreeram04 

I need to do further calculations on this so cant change datatype