Group by primary field then provide the days between start date and end date then sum hours

[Bishop1999]

I have a very large data set consisting of members yearly work activity.
The primary field is the work order number that may have one to many jobs that may be completed in one to many days.
What I seek is to group / break the work order in power query then provide the days elapsed, and total hours spent at the bottom of the group using power query. In Excel I can use the subtotal feature and manually subtract the begin date from the end date for each occurrence but I wouldn't be taking advantage of PQ. Any Ideal

Sample data
[TABLE="width: 676"]
<tbody>[TR]
[TD]WO #[/TD]
[TD]EMP[/TD]
[TD]Date Start[/TD]
[TD]Date End[/TD]
[TD] Time Start[/TD]
[TD]Time End[/TD]
[TD]Total Hrs[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John, Doe[/TD]
[TD]12/4/2017[/TD]
[TD]12/4/2017[/TD]
[TD]13:56[/TD]
[TD]14:48[/TD]
[TD]0.85778[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John, Doe[/TD]
[TD]12/5/2017[/TD]
[TD]12/5/2017[/TD]
[TD]10:03[/TD]
[TD]10:33[/TD]
[TD]0.50583[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John, Doe[/TD]
[TD]12/5/2017[/TD]
[TD]12/5/2017[/TD]
[TD]10:33[/TD]
[TD]10:33[/TD]
[TD]0.00306[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John, Doe[/TD]
[TD]12/6/2017[/TD]
[TD]12/6/2017[/TD]
[TD]7:00[/TD]
[TD]8:53[/TD]
[TD]1:53[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John, Doe[/TD]
[TD]12/6/2017[/TD]
[TD]12/6/2017[/TD]
[TD]8:00[/TD]
[TD]9:27[/TD]
[TD]1:27[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John, Doe[/TD]
[TD]12/6/2017[/TD]
[TD]12/6/2017[/TD]
[TD]9:30[/TD]
[TD]16:00[/TD]
[TD]6:30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John, Doe[/TD]
[TD]12/13/2017[/TD]
[TD]12/13/2017[/TD]
[TD]7:00[/TD]
[TD]16:30[/TD]
[TD]9:30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John, Doe[/TD]
[TD]12/30/2017[/TD]
[TD]12/30/2017[/TD]
[TD]15:00[/TD]
[TD]15:54[/TD]
[TD]0:54[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 487"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

this will give you the total minutes spent by Work Order. Not sure how to get the elapsed days. Hopefully, someone else will jump in for that

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Start", type date}, {"Date End", type date}, {"Time Start", type time}, {"Time End", type time}}),
    #"Inserted Rounding" = Table.AddColumn(#"Changed Type", "Round", each Number.Round([Total Hrs], 2), type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Rounding", "Custom", each [Total Hrs]*60),
    #"Inserted Rounding1" = Table.AddColumn(#"Added Custom", "Round.1", each Number.Round([Custom], 0), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Rounding1",{"Total Hrs", "Round", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Round.1", "Minutes"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Time Start", "Time End"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"WO #"}, {{"Sum Minutes", each List.Sum([Minutes]), type number}})
in
    #"Grouped Rows"

Look at this link for start and end date elapsed days

https://www.poweredsolutions.co/2018/03/05/calculate-days-dates-using-power-query/

 

[sandy666]

shorter version

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"WO #"}, {{"Sum", each List.Sum([Total Hrs]), type number}}),
    #"Multiplied Column" = Table.TransformColumns(#"Grouped Rows", {{"Sum", each _ * 60, type number}}),
    #"Rounded Off" = Table.TransformColumns(#"Multiplied Column",{{"Sum", each Number.Round(_, 0), type number}})
in
    #"Rounded Off"[/SIZE]

and link from post above

 

[sandy666]

maybe

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]WO #[/td][td=bgcolor:#70AD47]Minutes[/td][td=bgcolor:#70AD47]Days[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

82​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]

51​

[/td][td]

24​

[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"WO #"}, {{"Minutes", each List.Sum([Total Hrs]), type number}, {"Min", each List.Min([Date Start]), type datetime}, {"Max", each List.Max([Date End]), type datetime}}),
    #"Multiplied Column" = Table.TransformColumns(#"Grouped Rows", {{"Minutes", each _ * 60, type number}}),
    #"Rounded Off" = Table.TransformColumns(#"Multiplied Column",{{"Minutes", each Number.Round(_, 0), type number}}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Rounded Off", "Days", each Duration.Days([Max] - [Min]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Date Subtraction",{"Min", "Max"})
in
    #"Removed Columns"[/SIZE]

or I misunderstood