Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

replace numeric values with 0 in Power Query

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...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, Anonymous 

    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's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Try this M code

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

Hope this helps.