Custom Column in Power Query Editor

[nleuck]

Hello All,

So I haven't found much on how to deal with nulls in Custom Column formula and could use some help. I have two Date/Time columns: CreatedDateTime and ResolvedDateTime. I'm trying to find the duration between these columns, but I have null values when there isn't a ResolvedDateTime. I could easily replace those null with the current date/time, but I don't want to show to ResolvedDateTime if it hasn't actually been resolved.

ResolvedTime Custom Column formula:

=Duration.ToText(if [ResolvedDateTime] = null then

Date.Time.LocalNow - [CreatedDateTime] else

[ResolvedDateTime] - [CreatedDateTime])

The issue is I'm getting errors for the rows where ResolvedDateTime is null. I thought the formula would take care of that.

Any ideas?

Thanks!

[Mariusz]

Hi @nleuck 

Try this, if you don't want to calculate if null

try Duration.ToText( [ResolvedDateTime] - [CreatedDateTime] ) otherwise null

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[nleuck]

@Mariusz 

I don't want them to be null. If there isn't a ResolvedDateTime that means the issue hasn't been resolved yet. I want to get the duration still. For example, if an issue was created 4/23/2020 4:00pm and hasn't been resolved, I want to know the duration between created date and current data/time.

[MFelix]

Hi  @nleuck ,

try the following custom column:

if [ResolvedDateTime] = null then DateTime.LocalNow() - [CreatedDateTime] else [ResolvedDateTime]-[CreatedDateTime]

[nleuck]

@MFelix 

That is the formula I'm using except mine is inside Duration.ToText().

[MFelix · Accepted solution]

Hi @nleuck ,

It's not the same formula if you check, you are not opening and closing brackets after the DateTime.LocalNow

Add the brackets and check if the error is gone, if not please check the error code on the bottom and tell me the result.

[nleuck]

@MFelix 

I see that now. I didn't realize the () made a difference. Is there a way to Round the seconds up though? I'm getting some weird results. Example: 3.12:40:18.1204227

Thanks again for your help!

[MFelix]

Hi  @nleuck ,

try the following code:

Text.BeforeDelimiter(

Duration.ToText(if [ResolvedDateTime] = null then DateTime.LocalNow() - [CreatedDateTime] else [ResolvedDateTime]-[CreatedDateTime]), ".",1)

Then convert column to duration format

[nleuck]

@MFelix 

Still gives me decmial for seconds. It only does it for those that the ResolvedDateTime is null and I'm using DateTime.LocalNow() - [CreatedDateTime]

[MFelix]

Hi @nleuck ,

On the test I made it took out decimals from the ones that were nulls. Can you share the complete formula? Not doubting you but want to check the syntax.

[nleuck]

@MFelix 

Here is the formula:

Text.BeforeDelimiter(Duration.ToText(if [ResolvedDateTime] = null then
DateTime.LocalNow() - [CreatedDateTime] else
[ResolvedDateTime] - [CreatedDateTime]), ".", 1)

There are 4 rows where it's still giving me decimals.

[MFelix]

Hi @nleuck ,

Believe that the issue is related with the number below one day you need to redo your column to:

if Duration.Days( if [ResolvedDateTime] = null then
DateTime.LocalNow() - [CreatedDateTime] else
[ResolvedDateTime] - [CreatedDateTime] ) <1
then
Text.BeforeDelimiter(Duration.ToText(if [ResolvedDateTime] = null then
DateTime.LocalNow() - [CreatedDateTime] else
[ResolvedDateTime] - [CreatedDateTime]), ".", 0) 
else
Text.BeforeDelimiter(Duration.ToText(if [ResolvedDateTime] = null then
DateTime.LocalNow() - [CreatedDateTime] else
[ResolvedDateTime] - [CreatedDateTime]), ".", 1)

Should Work as expected.