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 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.

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.

5 Replies

  • artemus's avatar
    artemus
    Icon for Microsoft Employee rankMicrosoft Employee
    3 years ago

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

  • 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. 

       

       

  • Vickar's avatar
    Vickar
    Icon for Advocate II rankAdvocate II
    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.