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”
Try this, first select the columns and:
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?
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!