min and max date

[Imran Azam]

HI GUYS,

I am trying to get the min and max date for a customer from the data below

		Name	item	qty	start date	end date
		Alan	S	3	01/09/2013 <tbody> </tbody>	31/12/2014
		Alan	S	5	01/11/2015	31/12/2016
		Alan	S	2	01/01/2016 <tbody> </tbody>	31/12/2017

<tbody>
</tbody>

i want so show the below

which is the name the item , sum of qty and min start date and max end date

Name	item	QTY	start date	end date
Alan	S	10	01/09/2013 <tbody> </tbody>	31/12/2017

<tbody>
</tbody>

how can i get the min and max dates?

 

[sandy666]

with PowerQuery (Get&Transform)

Name	item	qty	start date	end date		Name	item	qty	Min date	Max date
Alan	S	3​	01/09/2013​	31/12/2014​		Alan	S	10​	01/09/2013​	31/12/2017​
Alan	S	5​	01/11/2015​	31/12/2016​
Alan	S	2​	01/01/2016​	31/12/2017​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"item", type text}, {"qty", Int64.Type}, {"start date", type date}, {"end date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "item"}, {{"qty", each List.Sum([qty]), type number}, {"Min date", each List.Min([start date]), type date}, {"Max date", each List.Max([end date]), type date}})
in
    #"Grouped Rows"[/SIZE]

 

[Joe4]

For a strictly Excel solution, see this: https://exceljet.net/formula/minimum-value-if