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

Bishop1999

New Member
Joined
May 31, 2013
Messages
8
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

WO #EMPDate StartDate End Time StartTime EndTotal Hrs
1John, Doe12/4/201712/4/201713:5614:480.85778
1John, Doe12/5/201712/5/201710:0310:330.50583
1John, Doe12/5/201712/5/201710:3310:330.00306
2John, Doe12/6/201712/6/20177:008:531:53
2John, Doe12/6/201712/6/20178:009:271:27
2John, Doe12/6/201712/6/20179:3016:006:30
2John, Doe12/13/201712/13/20177:0016:309:30
2John, Doe12/30/201712/30/201715:0015:540:54

<tbody>
</tbody>


<tbody>
</tbody>
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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

Code:
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/
 
Last edited:
Upvote 0
shorter version

Code:
[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
 
Upvote 0
maybe

WO #MinutesDays
1​
82​
1​
2​
51​
24​

Code:
[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
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top