Forum Discussion

tgarthaffner's avatar
tgarthaffner
New Member
2 years ago
Solved

Change ANY date to Text

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. 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

3 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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's avatar
      lbendlin
      Super User
      2 years ago

      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's avatar
    Anonymous
    Not applicable
    2 years ago

    In a custom column, include

     

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

     

    --Nate