Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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 |
12345 | 4/2/2017 9:00:00 PM |
67890 | 5/12/2018 9:00:00 PM |
Any assistance would be highly appreciated.
Solved! Go to Solution.
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.
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.
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"
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.
what error are you getting? Adding a custom column with each _ + #duration(0, 3, 0, 0) should work.