Forum Discussion
Collating tricky data
Hi Riny, my conversion is definitely incorrect
I did specify d/m/y but I must have done something wrong.
After correcting it, I get the same results as you.
Anyway, PowerQuery is a very good approach.
Thanks HansVogelaar & Riny_van_Eekelen. I thought I was good on excel but you have both shown me up!! I do still have one concern. It appears that data provided is 1/2 hourly data, not hourly. For example, on 24th February the peak hourly usage is 45.2kW (see below).
| 22.9 | 24/02/2020 16:00 | 1-Monday |
| 22.3 | 24/02/2020 16:30 | 1-Monday |
According to the pivot table the peak is 27.80 on Wednesday at 1500 hours. Have I missed something?
| Months | Feb | |||||||
| Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | Grand Total | |
| 00 | 4.44 | 4.45 | 4.49 | 4.65 | 4.81 | 4.49 | 4.36 | 4.52 |
| 01 | 4.07 | 4.33 | 4.30 | 4.31 | 4.53 | 4.19 | 4.08 | 4.25 |
| 02 | 4.37 | 4.49 | 4.65 | 4.74 | 4.79 | 4.43 | 4.45 | 4.55 |
| 03 | 4.33 | 4.36 | 4.41 | 4.59 | 4.53 | 4.61 | 4.24 | 4.43 |
| 04 | 5.93 | 6.59 | 5.81 | 6.55 | 6.19 | 5.19 | 4.45 | 5.82 |
| 05 | 13.50 | 13.60 | 15.71 | 16.79 | 16.46 | 5.24 | 4.30 | 12.27 |
| 06 | 17.58 | 18.56 | 17.84 | 18.14 | 16.14 | 4.93 | 4.24 | 14.04 |
| 07 | 18.53 | 19.38 | 18.48 | 18.25 | 17.10 | 4.55 | 4.53 | 14.54 |
| 08 | 20.87 | 20.28 | 19.58 | 19.74 | 19.54 | 5.66 | 5.23 | 16.01 |
| 09 | 22.85 | 21.35 | 21.18 | 22.11 | 21.65 | 5.79 | 5.16 | 17.35 |
| 10 | 23.88 | 21.39 | 21.34 | 22.58 | 21.86 | 5.24 | 5.69 | 17.65 |
| 11 | 23.77 | 23.18 | 23.68 | 24.94 | 24.90 | 5.41 | 5.55 | 18.95 |
| 12 | 24.77 | 23.73 | 24.46 | 23.56 | 24.76 | 5.29 | 6.09 | 19.15 |
| 13 | 25.48 | 25.38 | 25.84 | 25.15 | 25.85 | 5.75 | 5.00 | 19.97 |
| 14 | 25.32 | 24.44 | 25.89 | 25.44 | 25.89 | 5.81 | 4.91 | 19.87 |
| 15 | 26.61 | 26.38 | 27.80 | 27.54 | 26.51 | 5.40 | 4.64 | 20.90 |
| 16 | 24.09 | 23.48 | 25.01 | 25.44 | 24.29 | 5.06 | 4.64 | 19.04 |
| 17 | 15.42 | 15.86 | 16.13 | 16.69 | 14.49 | 4.69 | 4.86 | 12.69 |
| 18 | 8.01 | 8.56 | 8.04 | 9.00 | 6.41 | 4.38 | 4.58 | 7.03 |
| 19 | 7.10 | 8.61 | 7.53 | 8.26 | 5.35 | 4.19 | 4.35 | 6.51 |
| 20 | 7.25 | 7.55 | 7.04 | 7.53 | 5.26 | 4.33 | 4.40 | 6.23 |
| 21 | 4.86 | 4.43 | 4.80 | 4.88 | 4.25 | 4.14 | 4.18 | 4.52 |
| 22 | 4.42 | 4.21 | 4.46 | 4.36 | 4.24 | 4.00 | 4.11 | 4.26 |
| 23 | 4.32 | 4.15 | 4.39 | 4.45 | 4.26 | 4.05 | 4.15 | 4.26 |
The workbook in my previous reply calculates the average. Here is a version that calculates the sum again. And it filters by year and by month.
- Thanks so much Hans. That looks correct.
Moving forward, when processing similar sets of data, I can use the =WEEKDAY(B40,2)&"-"&TEXT(B40,"dddd") formula to sort the date data. The next question is how to run pivot tables or powerquery and to condense the 1/2hourly data into hourly data. Would you have a "template" I could use in a case like this?
Again, thanks so much for your contribution!!Pivot tables have an option to group date fields by years, months, days, hours, minutes and seconds.
You'll see these options when you right-click any of the dates or times in the pivot table and select Group... from the context menu.
For PowerPivot, I refer you to Riny van Eekelen.