PQ - How much was sold in an hour
How much was sold in an hour
| Time |
Sold |
________ |
Start of Hour |
End of Hour |
Sold |
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 |
|
|
|
|
Code:
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
