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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Vickar
Advocate I
Advocate I

Power Query : Add 3 hours to each row of a table's column whose data type is datetime

I'm trying to add 3 hours to the column [Contract_Signed_Date] whose data type is datetime. I'm unable to do so. I tried using #duration(0,3,0,0) for each row, but it doesn't work. Below is the sample table.

 

the desired output should be 4/3/2017 12:00:00 AM for first row, and 5/13/2018 12:00:00 AM for the second row

 

Contract#Contract_Signed_Date
123454/2/2017 9:00:00 PM
678905/12/2018 9:00:00 PM

 

Any assistance would be highly appreciated.

1 ACCEPTED SOLUTION
Vickar
Advocate I
Advocate I

I resolved it myself, by adding individual columns for Date and Time from DateTime column and then incrementing the Date by 1 day and Time by 3 hours as below
Transformation to Add 1 Day to the extracted Date column

 

#"Transform Date" = Table.TransformColumns(#"Extract Signed Time",{{"Signed_Date",each Date.AddDays(_,1),type date}})

 

Transformation to Add 3 hours to the extracted Time column

 

#"Transform Time" = Table.TransformColumns(#"Transform Date",{{"Signed_Time",each _ + #duration(0,3,0,0),type time}})

 

hope this helps others facing similar issue while connecting to a PostgreSQL db instance hosted on linux server.

View solution in original post

5 REPLIES 5
Vickar
Advocate I
Advocate I

I resolved it myself, by adding individual columns for Date and Time from DateTime column and then incrementing the Date by 1 day and Time by 3 hours as below
Transformation to Add 1 Day to the extracted Date column

 

#"Transform Date" = Table.TransformColumns(#"Extract Signed Time",{{"Signed_Date",each Date.AddDays(_,1),type date}})

 

Transformation to Add 3 hours to the extracted Time column

 

#"Transform Time" = Table.TransformColumns(#"Transform Date",{{"Signed_Time",each _ + #duration(0,3,0,0),type time}})

 

hope this helps others facing similar issue while connecting to a PostgreSQL db instance hosted on linux server.

Imrans123
Advocate V
Advocate V

Add Column>Custom Column and make the formula 

[Contract_Signed_Date]+#duration(0,3,0,0)

 

Or See below screenshot and advanced editor steps for reference

 

Imrans123_1-1665009077379.png

let
Source =......,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract_Signed_Date", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Contract_Signed_Date]+#duration(0,3,0,0)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type datetime}})
in
#"Changed Type1"

 

below is the error that I encounter when I add a custom column with #duration(0,3,0,0)  to the actual sample table sourced from a PostgreSQL data source. 

Vickar_0-1665045061205.png

 

 

artemus
Microsoft Employee
Microsoft Employee

what error are you getting? Adding a custom column with each _ + #duration(0, 3, 0, 0) should work.

I've posted the error in response to @Imrans123's response.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors