replace numeric values with 0 in Power Query

Hi Anonymous,

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,

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.

Hi,

Try this M code

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

Hope this helps.