Forum Discussion
Custom columns with timestamps and date transformations
Hi All,
I am working on a report in which we have 3 columns containing timestamps - Opened, Resolved & Closed. Our concern is with the null values in Resolved column. I am trying to create a custom column where If Resolved is null and Closed is null then Resolved row should contain the same value as Opened and If Resolved is null and Closed is having a value then resolved should contain closed date - 7days
Eg -
| Opened | Resolved | Closed | Expected column |
| 2017-03-29 08:44:15 | 2017-04-04 08:44:15 | Resolved | |
| 2/29/2017 8:41:43 AM | null | 2017-03-15 08:41:43 | closed -7 days |
| 2017-03-29 08:33:18 | null | null | opened |
First, I have created a custom column - resolved -1 where I used the formula as follows - "resolved-1", each if [Resolved] = null and [Closed]=null then [Opened]else [Resolved])
Then I tried to create another custom column from Resolved 1 with formula - ("Final resolve", each if [#"resolved-1"] =null then [Closed] - 7 else [#"resolved-1"])
Now I am getting an error for all the null values in the Final resolve column I am getting this error -
Expression.Error: We cannot apply operator - to types DateTime and Number.
Details:
Operator=-
Left=4/5/2017 9:47:46 AM
Right=7
Any response is highly appreciated.
Regards,
Rohith
DanielV91 there are some superfluous checks in your code: if a condition is not true, then you don't need to check in the else part if the condition is false.
Assuming the data is correct, your code can be shortened to:
if [Resolved] <> null then [Resolved] else if [Closed] = null then [Opened] else Date.AddDays([Closed],-7)
Note: Date.AddDays can also be used with datetime type data, so no need to convert to date.
4 Replies
Hello,
First of all, 2017 is not a leap year ::smileyvery-happy: so 29 of February doesn't exist.
Second, make sure to properly format that date type; I suggest you only keep the date and get rid of the hours.
I managed to achive your requirement with the following query:
if [Resolved] <> null then [Resolved] else if [Resolved] = null and [Closed] = null then [Opened] else if [Closed] <> null then Date.AddDays([Closed],-7) else "Check Data!"
See the result in the following picture and let me know if this helped you
DanielV91 there are some superfluous checks in your code: if a condition is not true, then you don't need to check in the else part if the condition is false.
Assuming the data is correct, your code can be shortened to:
if [Resolved] <> null then [Resolved] else if [Closed] = null then [Opened] else Date.AddDays([Closed],-7)
Note: Date.AddDays can also be used with datetime type data, so no need to convert to date.
MarcelBeug Thanks for noticing, I should have paid more attention, my bad.
