Conditional replace apply to multiple columns in Power Query

[navafolk]

Hi pros,

My input table looks like

Date	SaleA	SaleB	SaleC	SaleD
01-Apr	5000
02-Apr		2000	3000	4000
03-Apr	502.12	203.45
04-Apr	-1000
05-Apr			115.06
06-Apr		111.11		117.54
07-Apr
08-Apr	25.82

I need to clean all deposits which are multiple of 1000, e.g. 5000, -1000, 2000, 3000 etc. in the table.

Power Query 1 column conditional replacement works for me:

= Table.ReplaceValue(#"Changed Type",each [SaleA],each if Number.Mod([SaleA],1000)=0 then 0 else [SaleA],Replacer.ReplaceValue,{"SaleA"})

but I cannot do one-by-one for too many columns, they are almost whole table except the Date column.

Please help me with this situation in Power Query, thank you. 

[ThxAlot · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9LDoAgDAXvwhpLW1rgLsYbGHfeXz6JEoXE1aTt5L10XQ0jixNHxhpFxIzj3PcXNnuL/Jy4+b5BCjrR10QG4qp6EJ2nSt4t9Kf/HWINkQKGgRp6h4ConyOo9HIcN39TU3lHIfHE3C4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, SaleA = _t, SaleB = _t, SaleC = _t, SaleD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"SaleA", type number}, {"SaleB", type number}, {"SaleC", type number}, {"SaleD", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type", null, null, (x,y,z) => if Number.Mod(x,1000)=0 then null else x, List.Skip(Table.ColumnNames(#"Changed Type")))
in
    #"Replaced Value"

[navafolk]

Thank you @ThxAlot for your clear instruction with this strange syntax.

[ronrsnfld]

I suggest a small change to retain the data type for the replaced columns:

(x,y,z)as nullable number => if Number.Mod(x,1000)=0 then 0 else x,

[aduguid]

if Number.Mod([SaleA], 1000) = 0 then 0 else [SaleA]

To check if a value is divisible by 1000 and replace it with 0, you can use the modulo operation (Number.Mod) to determine if there's no remainder when dividing by 1000.

• Click on Add Column in the ribbon and then select Custom Column.
• In the Custom Column dialog, you can write a formula to handle the condition.