Conditional replace values

[Anonymous]

Hi, how can i replaced 0 to null on all columns but only for Category C and D in power query/M code?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVQ0lGyABFmIMIIRDgqxepEK4FYhiDCBESAsBNY3BjGBWFLEOEMV2+Eot5FKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Category = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Category", type text}})
in
#"Changed Type"

From:

Column1	Column2	Column3	Column4	Category
50	80	60	20	A
0	10	40	0	B
30	0	0	90	C
0	20	40	0	D

To:

Column1	Column2	Column3	Column4	Category
50	80	60	20	A
0	10	40	0	B
30	null	null	90	C
null	20	40	null	D

[KiwiPete · Accepted solution]

Try this if you do not wish to use the filetered table route. 

The [If Then] makes the Replace hugely powerfull but might be slower as it is compares at the record level.  Be very interested to see which is faster across your very large data set.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVQ0lGyABFmIMIIRDgqxepEK4FYhiDCBESAsBNY3BjGBWFLEOEMV2+Eot5FKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Category = _t]),
Columns = Table.ColumnNames(Source),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Category", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",0,each if [Category] = "C" or [Category] = "D" then "null" else 0 ,Replacer.ReplaceValue,{"Column1","Column2","Column3","Column4"})
in
#"Replaced Value"

[KiwiPete]

Split the table into two separate tables, one with CD, one without CD.  Replace all 0's across the with CD then rejoin the tables.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FilterNonD = Table.SelectRows(Source, each not ([Category] = "D" or [Category] = "C")),
FilterD = Table.SelectRows(Source, each [Category] = "D" or [Category] = "C"),
ReplaceZeros = Table.ReplaceValue(FilterD,0,null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4"}),
CombineTables = Table.Combine({FilterNonD, ReplaceZeros})
in
CombineTables

[Anonymous]

I actually have huge data of million rows, is there a way without the need to split table?

[CNENFRNL]

@Anonymous , you might want to try,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY65FcAwCEN3oXYRwFfKHFv4ef81EvnAScF/GEmGUihs5CgDERDgoOoKoWPAA6izzXU+UTtwmV9+/tvmPgPxhSYLqB8+yaNZH7HOFEcTWrvOCraiuZU/d/W5xnkTQ2RZiblSmiV1oT4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Category", type text}}),

    Columns = Table.ColumnNames(Source),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"ar", each _, type table [Column1=nullable number, Column2=nullable number, Column3=nullable number, Column4=nullable number, Category=nullable text]}}),

    #"Added Custom" = Table.AddColumn(
        #"Grouped Rows",
        "Custom",
        each if List.Contains({"C","D"},[Category]) then
            Table.ReplaceValue([ar],0,null,Replacer.ReplaceValue,Columns)
            else [ar]
    ),

    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", Columns, Columns)
in
    #"Expanded Custom"