How to replace value with conditional?

[Anonymous]

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.

[DataNinja777 · Accepted solution]

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]

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

Best,

LAXMAN 

[DataNinja777]

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]

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

I just wanna add a step for replace value. 

[DataNinja777 · Accepted solution]

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]

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"

[Anonymous]

Hi,

Thanks for the solution Laxmanjatoth  and DataNinja777  offered, and i want to offer some more information for user to refer to.

hello @Anonymous , you just change your code to the following.

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","zero cost",null,Replacer.ReplaceValue,{"Zero Cost"})

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.

[Anonymous]

Hi,

My query is opposite, any nemeric values or any other values need to be null and only 'Zero cost' should be remained as 'Zero cost'. 

[Anonymous · Accepted solution]

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.