Forum Discussion
Power Query changing timestamp
I have an excel sheet with an "Opened Date" column and a "Closed Date" column. Both columns have full date and timestamp. When importing into Power Query, the "Opened Date" changes from correct timestamp to all 12:00am, for the entire column. The "Closed Date" column does not change and shows correct timestamp, ie 6/22/2022 9:03:24 AM. Why is the Opened Date column getting changed to a default 12am timestamp? I need to have it the correct timestamp.Power Query timestamp
4 Replies
Anonymous I'm using the normal format for date in excel - Ex. 3/14/12 1:30PM. I have also tried it the default which displays as 3/14/12 18:30:02. Both have same results on Power Query side.
Adv Ed Code:
let
Source = Excel.Workbook(File.Contents(filename), null, true),
#"Page 1_Sheet" = Source{[Item="Page 1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Page 1_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Number", type text}, {"Opened", type date}, {"State", type text}, {"Opened by", type text}, {"Opened for", type text}, {"Subject person", type text}, {"Assignment group", type text}, {"Assigned to", type text}, {"HR service", type text}, {"Country", type text}, {"Parent", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Number", "Case Number"}, {"Opened", "Opened Date"}, {"Parent", "Region"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Closed", type datetime}, {"Opened Date", type datetime}, {"SLA", type number}, {"Updated", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Age Of Case (Still Open)", each Duration.Days(Duration.From(DateTime.LocalNow() - [Opened Date]))),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Opened Date", type datetime}})
in
#"Changed Type2"