cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ctedesco3307
Resolver II
Resolver II

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

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 

ctedesco3307_0-1655405750815.png

 

1 ACCEPTED SOLUTION
tomfox
Super User
Super User

Hi @ctedesco3307 ,

 

Before:

tomfox_0-1655408924593.png

 

 

After:

tomfox_1-1655408939621.png

 

 

Here the code in advanced editor:

tomfox_2-1655408992962.png

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
tomfox
Super User
Super User

Hi @ctedesco3307 ,

 

Before:

tomfox_0-1655408924593.png

 

 

After:

tomfox_1-1655408939621.png

 

 

Here the code in advanced editor:

tomfox_2-1655408992962.png

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors