Forum Discussion

navafolk's avatar
navafolk
Icon for Helper IV rankHelper IV
2 years ago
Solved

Conditional replace apply to multiple columns in Power Query

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  ...
Need Help
  • ThxAlot's avatar
    ThxAlot
    2 years ago
    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"

aduguid's avatar
aduguid
Icon for Memorable Member rankMemorable Member
2 years ago

 

 

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.