PDA

View Full Version : PQ - How much was sold in an hour



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