Forum Discussion
Collating tricky data
In the attached version, I have added a Day of Week column to the source data, and used that in the pivot table.
I created a chart based on the pivot table.
HansVogelaar Hi Hans! You may have noticed that I'm a great fan of Power Query (and Power Pivot). That's why I decided to "attack" this question using these tools. The March graph that I produced indicated clear discrepancies compared with yours. Most prominent for Saturdays and Sundays. Your lines came out much higher than mine. Have attached my file.
When you converted the text "date/time" to real dates and times, the date that I believe to be Sunday 1 March 2020 became Friday 3 January 2020 in your data (see picture). And that happened for all the dates where the day and month numbers are 12 or below.
Up to KITSONA to decide which approach he wants to take. Yours with corrected dates (probably easier) or mine with PQ/PP (more secure but probably more difficult to grasp if you are not familiar with it).
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.
