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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Employee
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors