Forum Discussion

Vickar's avatar
Vickar
Icon for Advocate II rankAdvocate II
3 years ago
Solved

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 sampl...
Need Help
  • Vickar's avatar
    Vickar
    3 years ago

    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's avatar
Imrans123
Icon for Advocate V rankAdvocate V
3 years ago

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

 

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"

 

Vickar's avatar
Vickar
Icon for Advocate II rankAdvocate II
3 years ago

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.