Have a spreadsheet where im trying to calculate costs for times of day, that also change at the weekend. (I can do it with vlookups but there will be more criteria like package type)
(Day linked to WeekDay to Weekday to TariffType)
Im trying to create a calculated field in the HoursUsed table that multiplies Hours Used Column by Cost Column (based on day of week and time of day)
If i do it with just time and not the day of week its
=TariffCost[Cost]*HoursUsed[Hours Used]
I think i need to use the filter function to filter the TariffType table or possibly the DayOfWeek table but am unsure how to use it in power query
Any suggestions/further reading, new to power query and everything im googling is not helping
Thanks
PQExample.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | Date | Time Of Day | Hours Used | Day | WeekDay | Weekday | Time | TariffType | TariffType | Cost | ||||||
2 | 01/12/2022 | 00:00 | 1 | 5 | 1 | 5 | 00:00 | Medium | Very Low | 25 | ||||||
3 | 01/12/2022 | 01:00 | 3 | 5 | 2 | 5 | 01:00 | Medium | Low | 50 | ||||||
4 | 01/12/2022 | 02:00 | 4 | 5 | 3 | 5 | 02:00 | Medium | Medium | 75 | ||||||
5 | 01/12/2022 | 03:00 | 2 | 5 | 4 | 5 | 03:00 | Medium | High | 150 | ||||||
6 | 01/12/2022 | 04:00 | 1 | 5 | 5 | 5 | 04:00 | Medium | Very High | 200 | ||||||
7 | 01/12/2022 | 05:00 | 5 | 5 | 6 | 5 | 05:00 | Medium | ||||||||
8 | 01/12/2022 | 06:00 | 2 | 5 | 7 | 5 | 06:00 | Medium | ||||||||
9 | 01/12/2022 | 07:00 | 8 | 5 | 5 | 07:00 | Medium | |||||||||
10 | 01/12/2022 | 08:00 | 9 | 5 | 5 | 08:00 | Medium | |||||||||
11 | 01/12/2022 | 09:00 | 8 | 5 | 5 | 09:00 | Very Low | |||||||||
12 | 01/12/2022 | 10:00 | 7 | 5 | 5 | 10:00 | Very Low | |||||||||
13 | 01/12/2022 | 11:00 | 7 | 5 | 5 | 11:00 | Very Low | |||||||||
14 | 01/12/2022 | 12:00 | 8 | 5 | 5 | 12:00 | Very Low | |||||||||
15 | 01/12/2022 | 13:00 | 9 | 5 | 5 | 13:00 | Very Low | |||||||||
16 | 01/12/2022 | 14:00 | 6 | 5 | 5 | 14:00 | Very Low | |||||||||
17 | 01/12/2022 | 15:00 | 5 | 5 | 5 | 15:00 | Very Low | |||||||||
18 | 01/12/2022 | 16:00 | 9 | 5 | 5 | 16:00 | Very Low | |||||||||
19 | 01/12/2022 | 17:00 | 9 | 5 | 5 | 17:00 | Very Low | |||||||||
20 | 01/12/2022 | 18:00 | 5 | 5 | 5 | 18:00 | Low | |||||||||
21 | 01/12/2022 | 19:00 | 4 | 5 | 5 | 19:00 | Low | |||||||||
22 | 01/12/2022 | 20:00 | 3 | 5 | 5 | 20:00 | Low | |||||||||
23 | 01/12/2022 | 21:00 | 1 | 5 | 5 | 21:00 | Low | |||||||||
24 | 01/12/2022 | 22:00 | 2 | 5 | 5 | 22:00 | Medium | |||||||||
25 | 01/12/2022 | 23:00 | 2 | 5 | 5 | 23:00 | Medium | |||||||||
26 | 02/12/2022 | 00:00 | 2 | 6 | 6 | 00:00 | Medium | |||||||||
27 | 02/12/2022 | 01:00 | 2 | 6 | 6 | 01:00 | Medium | |||||||||
28 | 02/12/2022 | 02:00 | 3 | 6 | 6 | 02:00 | Medium | |||||||||
29 | 02/12/2022 | 03:00 | 4 | 6 | 6 | 03:00 | Medium | |||||||||
30 | 02/12/2022 | 04:00 | 2 | 6 | 6 | 04:00 | Medium | |||||||||
31 | 02/12/2022 | 05:00 | 4 | 6 | 6 | 05:00 | Medium | |||||||||
32 | 02/12/2022 | 06:00 | 1 | 6 | 6 | 06:00 | Medium | |||||||||
33 | 02/12/2022 | 07:00 | 7 | 6 | 6 | 07:00 | Medium | |||||||||
34 | 02/12/2022 | 08:00 | 10 | 6 | 6 | 08:00 | Medium | |||||||||
35 | 02/12/2022 | 09:00 | 8 | 6 | 6 | 09:00 | Very Low | |||||||||
36 | 02/12/2022 | 10:00 | 7 | 6 | 6 | 10:00 | Very Low | |||||||||
37 | 02/12/2022 | 11:00 | 6 | 6 | 6 | 11:00 | Very Low | |||||||||
38 | 02/12/2022 | 12:00 | 7 | 6 | 6 | 12:00 | Very Low | |||||||||
39 | 02/12/2022 | 13:00 | 7 | 6 | 6 | 13:00 | Very Low | |||||||||
40 | 02/12/2022 | 14:00 | 5 | 6 | 6 | 14:00 | Very Low | |||||||||
41 | 02/12/2022 | 15:00 | 8 | 6 | 6 | 15:00 | Very Low | |||||||||
42 | 02/12/2022 | 16:00 | 9 | 6 | 6 | 16:00 | Very Low | |||||||||
43 | 02/12/2022 | 17:00 | 9 | 6 | 6 | 17:00 | Very Low | |||||||||
44 | 02/12/2022 | 18:00 | 6 | 6 | 6 | 18:00 | Low | |||||||||
45 | 02/12/2022 | 19:00 | 2 | 6 | 6 | 19:00 | Low | |||||||||
46 | 02/12/2022 | 20:00 | 1 | 6 | 6 | 20:00 | Low | |||||||||
47 | 02/12/2022 | 21:00 | 1 | 6 | 6 | 21:00 | Low | |||||||||
48 | 02/12/2022 | 22:00 | 2 | 6 | 6 | 22:00 | High | |||||||||
49 | 02/12/2022 | 23:00 | 1 | 6 | 6 | 23:00 | High | |||||||||
50 | 03/12/2022 | 00:00 | 2 | 7 | 7 | 00:00 | High | |||||||||
51 | 03/12/2022 | 01:00 | 6 | 7 | 7 | 01:00 | High | |||||||||
52 | 03/12/2022 | 02:00 | 2 | 7 | 7 | 02:00 | High | |||||||||
53 | 03/12/2022 | 03:00 | 1 | 7 | 7 | 03:00 | High | |||||||||
54 | 03/12/2022 | 04:00 | 1 | 7 | 7 | 04:00 | High | |||||||||
55 | 03/12/2022 | 05:00 | 4 | 7 | 7 | 05:00 | High | |||||||||
56 | 03/12/2022 | 06:00 | 4 | 7 | 7 | 06:00 | High | |||||||||
57 | 03/12/2022 | 07:00 | 4 | 7 | 7 | 07:00 | High | |||||||||
58 | 03/12/2022 | 08:00 | 7 | 7 | 7 | 08:00 | High | |||||||||
59 | 03/12/2022 | 09:00 | 2 | 7 | 7 | 09:00 | Medium | |||||||||
60 | 03/12/2022 | 10:00 | 2 | 7 | 7 | 10:00 | Medium | |||||||||
61 | 03/12/2022 | 11:00 | 2 | 7 | 7 | 11:00 | Medium | |||||||||
62 | 03/12/2022 | 12:00 | 4 | 7 | 7 | 12:00 | Medium | |||||||||
63 | 03/12/2022 | 13:00 | 1 | 7 | 7 | 13:00 | Medium | |||||||||
64 | 03/12/2022 | 14:00 | 5 | 7 | 7 | 14:00 | Medium | |||||||||
65 | 03/12/2022 | 15:00 | 5 | 7 | 7 | 15:00 | Medium | |||||||||
66 | 03/12/2022 | 16:00 | 9 | 7 | 7 | 16:00 | Medium | |||||||||
67 | 03/12/2022 | 17:00 | 9 | 7 | 7 | 17:00 | High | |||||||||
68 | 03/12/2022 | 18:00 | 5 | 7 | 7 | 18:00 | High | |||||||||
69 | 03/12/2022 | 19:00 | 4 | 7 | 7 | 19:00 | High | |||||||||
70 | 03/12/2022 | 20:00 | 3 | 7 | 7 | 20:00 | High | |||||||||
71 | 03/12/2022 | 21:00 | 1 | 7 | 7 | 21:00 | High | |||||||||
72 | 03/12/2022 | 22:00 | 2 | 7 | 7 | 22:00 | Very High | |||||||||
73 | 03/12/2022 | 23:00 | 2 | 7 | 7 | 23:00 | Very High | |||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E73 | E2 | =WEEKDAY([@Date]) |
(Day linked to WeekDay to Weekday to TariffType)
Im trying to create a calculated field in the HoursUsed table that multiplies Hours Used Column by Cost Column (based on day of week and time of day)
If i do it with just time and not the day of week its
=TariffCost[Cost]*HoursUsed[Hours Used]
I think i need to use the filter function to filter the TariffType table or possibly the DayOfWeek table but am unsure how to use it in power query
Any suggestions/further reading, new to power query and everything im googling is not helping
Thanks