Replace just the time value for some not all records in in date time field

[ctedesco3307]

Hello- I need a way to replace time on records that are not 12:00. Some of the records load with 12:00 AM as the time and some do not. For the ones that are not 12:00 AM, I want to replace the time on there with 12:00 AM.  I don't want to split up the date and time into seperate columns.

Any thoughts? thank you - in advance 

[tackytechtom · Accepted solution]

Hi @ctedesco3307 ,

Before:

After:

Here the code in advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJSMDCwAiOlWJ1oJSOsosYwUUMjK2NTmKgJXNTUytDcytAELGoKN8HcysACiMCiZpjmxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [datetime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"datetime", type datetime}}),
    #"Replace Values" = Table.ReplaceValue(#"Changed Type",each [datetime], each Date.From([datetime]) & Time.FromText("00:00:00") ,Replacer.ReplaceValue,{"datetime"})

in
    #"Replace Values"

Let me know if this solves your issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/