How can I conditionally replace values in a table query?

[Anonymous]

I have a table with two date columns, and a calculated column that calculates the number of working days between the value in each column

Date 1	Date 2	Working Days
2/5/2021	2/6/2021	4

I want to conditionally replace the values of the working days column afterwards using the criteria that if the date of both of the first two columns falls within the first 8 days of the month, it will correct the value to 0

Date 1	Date 2	Working Days
2/5/2021	2/6/2021	0

The issue I am struggling with is how to correct the function below. I believe my else may be incorrect. The else should be to just leave the value as is

= if Date.Day([Latest TM Action Date]) < 9 & Date.Day([Latest Submission Date]) < 9
then Table.ReplaceValue(#"Month Start Check", each #"Changed TM Process Days to number", 0, Replacer.ReplaceValue, {"TM Process Days"})
else #"Changed TM Process Days to number"

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

[CNENFRNL · Accepted solution]

@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"})

[Icey · Accepted solution]

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.

[CNENFRNL · Accepted solution]

@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"})