Power Query changing timestamp

[Anonymous]

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

[Anonymous]

Hi @Anonymous 

What is the timestamp format you have in Excel?

Also, Can you post the M-Code from the advance Editor.

[Anonymous]

@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"

[Anonymous · Accepted solution]

Hi @Anonymous 

Try changing the date to datetime in smallcase as mentioned in the below screenshot or remove the highlighted part in green alltogether. This should solve your issue.

[Anonymous]

@Anonymous , thank you that resolved it!!!!