Replace date.time values in one column conditioned upon ID in another column

[MJEnnis]

Due to a human data entry error that cannot be corrected at the source, I have to add one year to a date/time stamp for a about 10 events among 25,000. Fortunately, the events in question have a unique ID in the same table. When I try the following code, the date/times are not replaced in the new "replaced value" table. The [Date] column is only transformed from "date/time" data type to "any" (i.e., "ABC123") data type. 

= Table.ReplaceValue(#"Renamed Columns1", each [Date], each if [ID] = 36 then #datetime(2016, 02, 03, 0, 0, 0) else [Date], Replacer.ReplaceText,{"Date"})

I have also tried this method, also to no avail: 

= Table.ReplaceValue(#"Renamed Columns1", each [Date], each if [ID] = 36 then [Date] + #duration(365,0,0,0) else [Date], Replacer.ReplaceText,{"Date"})

Any suggestions for a quick fix?

[KNP]

Sometimes it's easier and cleaner to do it with a new column and then remove the old. 

Try this...

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMjZT0lEyNtA3NNI3MjAyUIrVAYqZg8QMoWKGSrGxAA==", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [id = _t, date = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"id", Int64.Type}, {"date", type date}}),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Custom",
    each if [id] = 36 then Date.AddYears([date], 1) else [date]
  )
in
  #"Added Custom"

Just remove the original date column when you're done with it.

[MJEnnis]

Thanks for the fast response! Quick question though: I have multiple relationships, calculations and reports dependent upon this column, so many that I cannot think of all of them off the top of my head. This is why I did not want to create a new custom column. If I delete the old column and use the same name for the new column, am I going to have to rebuild everything?

[KNP]

If you delete and rename you shouldn't have to redo anything.

I'll have a look at the syntax in your replace now.

[MJEnnis]

Also, can you think of any reason why the code I tried isn't working?

[KNP · Accepted solution]

Change your Replacer.ReplaceText with Replacer.ReplaceValue.

(I didn't test your duration addition)

= Table.ReplaceValue(#"Renamed Columns1", each [Date], each if [ID] = 36 then [Date] + #duration(365,0,0,0) else [Date], Replacer.ReplaceValue,{"Date"})

You will still run into issues with all of the types being reset to 'Any' with this method which is why I prefer the add column method.

See this post...

https://community.powerbi.com/t5/Desktop/Query-editor-replacing-values-based-on-another-column/td-p/...

[MJEnnis]

Okay... now I feel pretty dumb. 🙂 Thanks a lot! 

[KNP]

Don't, we all do it from time to time.  Glad I could help.