sandy666
05-11-2020, 12:30 AM
How much was sold in an hour
TimeSold________Start of HourEnd of HourSold
22:41:00
2
07:00:00
08:00:00
12
14:42:00
1
08:00:00
09:00:00
3
16:36:00
1
14:00:00
15:00:00
2
20:02:00
1
16:00:00
17:00:00
2
20:42:00
4
20:00:00
21:00:00
6
22:07:00
2
22:00:00
23:00:00
12
20:26:00
1
14:06:00
1
16:51:00
1
07:15:00
7
08:59:00
1
08:00:00
2
07:59:00
5
22:44:00
8
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"Time", type time}, {"Sold", Int64.Type}}),
Start = Table.AddColumn(Type, "Start of Hour", each Time.StartOfHour([Time]), type time),
End = Table.AddColumn(Start, "End of Hour", each Time.EndOfHour([Time]), type time),
Group = Table.Group(End, {"Start of Hour", "End of Hour"}, {{"Sold", each List.Sum([Sold]), type number}}),
Sort = Table.Sort(Group,{{"Start of Hour", Order.Ascending}})
in
Sort
TimeSold________Start of HourEnd of HourSold
22:41:00
2
07:00:00
08:00:00
12
14:42:00
1
08:00:00
09:00:00
3
16:36:00
1
14:00:00
15:00:00
2
20:02:00
1
16:00:00
17:00:00
2
20:42:00
4
20:00:00
21:00:00
6
22:07:00
2
22:00:00
23:00:00
12
20:26:00
1
14:06:00
1
16:51:00
1
07:15:00
7
08:59:00
1
08:00:00
2
07:59:00
5
22:44:00
8
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"Time", type time}, {"Sold", Int64.Type}}),
Start = Table.AddColumn(Type, "Start of Hour", each Time.StartOfHour([Time]), type time),
End = Table.AddColumn(Start, "End of Hour", each Time.EndOfHour([Time]), type time),
Group = Table.Group(End, {"Start of Hour", "End of Hour"}, {{"Sold", each List.Sum([Sold]), type number}}),
Sort = Table.Sort(Group,{{"Start of Hour", Order.Ascending}})
in
Sort