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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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