Adding Time to Date/Time Entries

Image 2020-07-15 13-50-09
I was given a dataset to work with in which - for some reason - the rows of date/time entries omit 12:00:00AM and for all the rest of the hourly entries I have date + time. For example: 7/17/2019 11:00:00 PM, then 7/17/2019, then 7/17/2019 1:00:00 AM…

Any suggestions on adding the “12:00:00 AM” to the rows which are omitting the time value?

Hi @lowen75,

Can you past the m code here ?

let
Source = Excel.Workbook(File.Contents("\\vmware-host\Shared Folders\Documents\Projects\POC\Power BI\drastic-clipping-last-year.xlsx"), null, true),
#"SQL Results_Sheet" = Source{[Item="SQL Results",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"SQL Results_Sheet", [PromoteAllScalars=true])

in
#“Promoted Headers”

@lowen75,

Try this, first select the columns and:
Capture

image It detects as Date/Time. Was about to split it into separate columns when I noticed the rows which should have 12:00:00 AM are missing from the dataset. Trying to find a way to add in 12:00:00 AM to the rows without a time value.

Or I guess I could split them, and then all the Errors in the new “Time” column, I could find and replace?

@lowen75,

What is the result after you change the data type to date/time ?

@ricardocamargos88 Odd, first it didn’t change anything. I changed data type to text and back to Date/Time and it populated the 12:00:00 AM. Looks good! Thank you!