Forum Discussion

navafolk's avatar
navafolk
Helper IV
2 years ago
Solved

Conditional replace apply to multiple columns in Power Query

Hi pros,

My input table looks like

DateSaleASaleBSaleCSaleD
01-Apr5000   
02-Apr 200030004000
03-Apr502.12203.45  
04-Apr-1000   
05-Apr  115.06 
06-Apr 111.11 117.54
07-Apr    
08-Apr25.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. 

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"

4 Replies

Replies have been turned off for this discussion
  • ThxAlot's avatar
    ThxAlot
    Super User
    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"

    • ronrsnfld's avatar
      ronrsnfld
      Super User
      2 years ago

      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's avatar
    aduguid
    Memorable 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.