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
Super User
Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

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
Super User

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)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

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
Super User
Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

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

Highlighted
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
Super User

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)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 285 members 2,733 guests
Please welcome our newest community members: