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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
RohithGn
Helper I
Helper I

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 ResolvedClosedExpected column
2017-03-29 08:44:152017-04-04 08:44:15 Resolved
2/29/2017  8:41:43 AMnull2017-03-15 08:41:43closed -7 days
2017-03-29 08:33:18nullnullopened

 

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

 

 

 pbi forum.PNG

 

 

 

 

Any response is highly appreciated.

 

Regards,

Rohith

 

 

 

1 ACCEPTED SOLUTION

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

Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
DanielV91
Frequent Visitor

Hello,

First of all, 2017 is not a leap year :Smiley Very 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

Capture.JPG

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

Specializing in Power Query Formula Language (M)

Thanks a ton @MarcelBeug your code is working as expected

 @DanielV91 Thanks a lot for the reply 🙂

@MarcelBeug Thanks for noticing, I should have paid more attention, my bad.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.