Change ANY date to Text

[tgarthaffner]

Good day. 

I have what seems to be a simple task but I cannot figure it out.

I have a list of dates when a task was completed. I do not want to list the date but simply an "X" to depict that the task was accomplished. So, I want to replace or transform ANY date in a column with the letter X, this can be another column if required. Please advise.

Thank you. 

[dufoq3 · Accepted solution]

Hi @tgarthaffner, replace yellow with your previous step reference and orange with column name where you want to replace dates.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVNzDUNzIwMlGK1YlW8sxTCCjKTy9KLS4G840s9A2MENIhqcUlSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ReplaceDatesWithX = Table.ReplaceValue(Source,
     null,
     "X",
     (x,y,z)=> if (try Date.From(x) otherwise false) is date then z else x,
     {"Column1"} )
in
    ReplaceDatesWithX

[lbendlin]

You can also combine these two approaches, no need for a full (x,y,z)  function.  But you must specify the culture.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjLVNzDUNzIwMlGK1YlW8sxTCCjKTy9KLS4G840s9A2MENIhqcUlSrGxAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    )
  ), 
  #"Replaced Value" = Table.ReplaceValue(
    Source, 
    each [Column1], 
    each 
      if (try Value.Is(Date.From([Column1], "en-GB"), type date) otherwise false) then
        "X"
      else
        [Column1], 
    Replacer.ReplaceValue, 
    {"Column1"}
  )
in
  #"Replaced Value"

[Anonymous]

In a custom column, include

each if Value.Type([DateColumn]) = type date then "X" else null

--Nate