Forum Discussion

smjzahid's avatar
smjzahid
Helper V
4 years ago
Solved

How do I remove non date time values from Date Time columns

I have a column as DateTime which is connected to excel files and users have keyed in TEXT value in source excel file.

 

This will keep happening again and again, How do I write a logic in Power Query that converts all TEXT values entered in this column to null

 

Need Help
Show and Tell
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Do a transformation step on your column and then tweak the code to look like this:

    = Table.TransformColumns(Source, {{"Planned Finish Date & Time",
    each try DateTime.FromText(_) otherwise null, type datetime}})

     

    Here's a full code sample you can paste into the Advanced Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MNQ1MA0xMLYyNrYyNNIzMjU3AAKlWJ1oJTARklpRohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Planned Finish Date & Time" = _t]),
    #"Text to Nulls" = Table.TransformColumns(Source,{{"Planned Finish Date & Time", each try DateTime.FromText(_) otherwise null, type datetime}})
in
    #"Text to Nulls"

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Do a transformation step on your column and then tweak the code to look like this:

    = Table.TransformColumns(Source, {{"Planned Finish Date & Time",
    each try DateTime.FromText(_) otherwise null, type datetime}})

     

    Here's a full code sample you can paste into the Advanced Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MNQ1MA0xMLYyNrYyNNIzMjU3AAKlWJ1oJTARklpRohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Planned Finish Date & Time" = _t]),
    #"Text to Nulls" = Table.TransformColumns(Source,{{"Planned Finish Date & Time", each try DateTime.FromText(_) otherwise null, type datetime}})
in
    #"Text to Nulls"
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi smjzahid ,

     

    You could follow AlexisOlson 's suggestion to use error handling function Try... Otherwise

    Or add a replaced error step after changing type:

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.