cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnJairoV Regular Visitor
Regular Visitor

non negative values in difference between date columns in PowerQuery

Hi to all!

 

I have this data (the letters in first row and numbers in first column are just for refference):

 

 ABCDEFGHI
1Other DateCont Date 1Cont Date 2Cont Date 3Prop Date 1Prop Date 2Prop Date 3Result 1Result 2
204/07/201604/05/201716/02/201720/09/201610/09/201704/02/201710/01/2017780
3nullnullnullnull23/11/201617/10/201622/01/2017nullnull
411/08/201605/08/2016nullnull11/01/2017nullnull0159
5null25/05/201610/08/201619/12/201613/02/201707/11/201717/01/2017null29
605/10/2016null06/07/2017null23/01/201703/09/201616/01/20172740
707/09/201604/08/201715/09/201620/04/2017nullnullnull8null
827/12/201516/07/2017null04/06/201626/09/2017nullnull16072

 

I want to calculate the difference between date columns in Power Query, Getting non-negative values.

 

If you calculate the results in Excel, you make this like (without take care about the null's in the Excel formula):

Result1 = MAX(0;MIN(B2;C2;D2)-A2)

Result2 = MAX(0;MIN(E2;F2;G2)-MAX(B2;C2;D2))

 

I need the results like I show in red text.  The date's columns are non-contiguous (if this information is needed). 

 

Thanks in advanced for your answers.  Blessings!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

Re: non negative values in difference between date columns in PowerQuery

You can basically use the same style when addiing column like this:

For Result1:

 

List.Max({	#duration(0,0,0,0),
List.Min({[Cont Date 1],[Cont Date 2],[Cont Date 3]}) - [Other Date]
})

And for Result2:

 List.Max({	#duration(0,0,0,0),
List.Min({[Prop Date 1],[Prop Date 2],[Prop Date 3]})-List.Max({[Cont Date 1],[Cont Date 2],[Cont Date 3]})
})

You create lists as your "ranges" to choose from and then you can use List.Min or List.Max as the equivalent to MIN or MAX in Excel.

To create a list, you wrap your comma-separated elements into curly brackets: { FirstElement, SecondElement... }

Also you have to format the first 0 as a duration for the formula to work properly.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Super User III
Super User III

Re: non negative values in difference between date columns in PowerQuery

Not so elegant any more then:

 

= Table.AddColumn(#"Added Custom", "Custom.1", each try if List.Max({null,
		List.Min({[Prop Date 1],[Prop Date 2],[Prop Date 3]})-List.Max({[Cont Date 1],[Cont Date 2],[Cont Date 3]})
	  }) > #duration(0,0,0,0) then List.Max({null,
		List.Min({[Prop Date 1],[Prop Date 2],[Prop Date 3]})-List.Max({[Cont Date 1],[Cont Date 2],[Cont Date 3]})
	  }) else 0 otherwise null)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

Re: non negative values in difference between date columns in PowerQuery

You can basically use the same style when addiing column like this:

For Result1:

 

List.Max({	#duration(0,0,0,0),
List.Min({[Cont Date 1],[Cont Date 2],[Cont Date 3]}) - [Other Date]
})

And for Result2:

 List.Max({	#duration(0,0,0,0),
List.Min({[Prop Date 1],[Prop Date 2],[Prop Date 3]})-List.Max({[Cont Date 1],[Cont Date 2],[Cont Date 3]})
})

You create lists as your "ranges" to choose from and then you can use List.Min or List.Max as the equivalent to MIN or MAX in Excel.

To create a list, you wrap your comma-separated elements into curly brackets: { FirstElement, SecondElement... }

Also you have to format the first 0 as a duration for the formula to work properly.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

JohnJairoV Regular Visitor
Regular Visitor

Re: non negative values in difference between date columns in PowerQuery

Great ImkeF!

 

Thanks for your answer.  But... If I want to get null results like the example in red, how do you get it?  Blessings!

Super User III
Super User III

Re: non negative values in difference between date columns in PowerQuery

Not so elegant any more then:

 

= Table.AddColumn(#"Added Custom", "Custom.1", each try if List.Max({null,
		List.Min({[Prop Date 1],[Prop Date 2],[Prop Date 3]})-List.Max({[Cont Date 1],[Cont Date 2],[Cont Date 3]})
	  }) > #duration(0,0,0,0) then List.Max({null,
		List.Min({[Prop Date 1],[Prop Date 2],[Prop Date 3]})-List.Max({[Cont Date 1],[Cont Date 2],[Cont Date 3]})
	  }) else 0 otherwise null)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors