How can I conditionally replace values in a table query?

Anonymous , you are one step away from your goal,

= Table.ReplaceValue(#"Preceding Step", each [TM Process Days], each if Date.Day([Latest TM Action Date]) < 9 and Date.Day([Latest Submission Date]) < 9
then 0 else [TM Process Days], Replacer.ReplaceValue, {"TM Process Days"})

Hi Anonymous ,

Please check if what CNENFRNL provided could meet your requirements.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ30jdV0oExzZRidaDixvrmMHFjfQuEuAmQowNjWiLETfUNYeJAppFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Latest TM Action Date" = _t, #"Latest Submission Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Latest TM Action Date", type date}, {"Latest Submission Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "TM Process Days", each Duration.Days([Latest Submission Date]-[Latest TM Action Date])),
    #"Replace Value" = Table.ReplaceValue(#"Added Custom", each [TM Process Days], each if Date.Day([Latest TM Action Date]) < 9 and Date.Day([Latest Submission Date]) < 9
then 0 else [TM Process Days], Replacer.ReplaceValue, {"TM Process Days"})

in
    #"Replace Value"

Alternatively, I can also simply change the values in both date columns... But again, this would require conditional correction. if both dates are within the first 8 days of month, then correct both columns so that both dates are first day of month

 

---

In addition, for your other needs, please check:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ30jdV0oExzZRidaDixvrmMHFjfQuEuAmQowNjWiLETfUNYeJAppFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Latest TM Action Date" = _t, #"Latest Submission Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Latest TM Action Date", type date}, {"Latest Submission Date", type date}}),
    #"Replace Value" = Table.ReplaceValue(
    Table.ReplaceValue(
      #"Changed Type",
      each [Latest TM Action Date],
      each 
        if Date.Day([Latest TM Action Date]) < 9 and Date.Day([Latest Submission Date]) < 9 then
          Date.StartOfMonth([Latest TM Action Date])
        else
          [Latest TM Action Date],
      Replacer.ReplaceValue,
      {"Latest TM Action Date"}
    ),
    each [Latest Submission Date],
    each 
      if Date.Day([Latest TM Action Date]) < 9 and Date.Day([Latest Submission Date]) < 9 then
        Date.StartOfMonth([Latest Submission Date])
      else
        [Latest Submission Date],
    Replacer.ReplaceValue,
    {"Latest Submission Date"}
  ),
    #"Added Custom" = Table.AddColumn(#"Replace Value", "TM Process Days", each Duration.Days([Latest Submission Date]-[Latest TM Action Date]))

in
    #"Added Custom"

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.