Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

How to replace value with conditional?

Hi,

 

I would like to replace all values to null if it is not 'zero cost'.

meaning any number value should be null. 

Please help Mcode.

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    Hi Anonymous ,

     

    = Table.ReplaceValue(
    YourTable,
    each _,
    each if _ = "zero cost" then _ else null,
    Replacer.ReplaceValue
)

    This M code transforms the Cost column by replacing any numeric value with null, while keeping the text "zero cost" unchanged.

     

    Best regards,

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous 

    You can try the following 

     

    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Zero Cost],each if [Zero Cost]<>"zero cost" then null else [Zero Cost] ,Replacer.ReplaceValue,{"Zero Cost"})

     

    and the solution DataNinja777 is right, you can also refer to it.

     

    #"Replaced Value1"=Table.ReplaceValue(
    #"Replaced Value",
    each _,
    each if _ = "zero cost" then _ else null,
    Replacer.ReplaceValue
)

     

     If the solutions DataNinja777  and i offered help you solve the problem, you can consider to accept them as solutions.

    Best Regards!

    Yolo Zhu

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

8 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    Hi Anonymous ,

     

    Here’s a simple M code formula to replace any numeric value with null, except for rows where the value is "zero cost" (assuming you have a column named Cost):

    M Code (Power Query) Transformation

    = Table.TransformColumns(
    YourTable,
    {
        {"Cost", each if _ = "zero cost" then _ else null}
    }
)

    Explanation:

    1. YourTable is your table name.
    2. The transformation applies to the Cost column.
    3. If the value in Cost is "zero cost", it keeps the value as is.
    4. If the value is any numeric value (or anything else), it replaces it with null.

    Best regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks, but can I replace values without creating another column such Cost you mentioned? 

      I just wanna add a step for replace value. 

      • DataNinja777's avatar
        DataNinja777
        Super User
        1 year ago

        Hi Anonymous ,

         

        = Table.ReplaceValue(
    YourTable,
    each _,
    each if _ = "zero cost" then _ else null,
    Replacer.ReplaceValue
)

        This M code transforms the Cost column by replacing any numeric value with null, while keeping the text "zero cost" unchanged.

         

        Best regards,

  • Laxmanjatoth's avatar
    Laxmanjatoth
    Resolver I
    1 year ago

    just right click on zero cost , replace the value with null , it is very simple 

    Best,

    LAXMAN