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.

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,

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Can you help me write a full code?

        My query is about 'Zero cost' column below. 

         

         

         

         

        #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Zero cost",null,Replacer.ReplaceValue,{"GR Date"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Zero cost", type text}, {"GR Date", type date}, {"Service#", Int64.Type}}),
        #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each [#"Service#"] <> null),
        #"Added Index" = Table.AddIndexColumn(#"Filtered Rows2", "Index", 1, 1, Int64.Type)
        in
        #"Added Index"