Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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 timestampPower Query timestampexcel timestamps.jpg

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

pranit828_0-1658167927911.png

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @Anonymous 

What is the timestamp format you have in Excel?

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

Anonymous
Not applicable

@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
Not applicable

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.

pranit828_0-1658167927911.png

 

Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors