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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors