Forum Discussion

MJEnnis's avatar
MJEnnis
Icon for Resolver III rankResolver III
4 years ago
Solved

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

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?

 

Need Help

7 Replies

  • KNP's avatar
    KNP
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      MJEnnis
      Icon for Resolver III rankResolver III
      4 years ago

      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?

      • MJEnnis's avatar
        MJEnnis
        Icon for Resolver III rankResolver III
        4 years ago

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