Forum Discussion
Collating tricky data
The values in the date/time column are text values. I converted them to real date/time values, then created a pivot table.
See the attached version.
Years and months are shown in the Rows area of the pivot table, hour of the day in the Columns area, and sum of usage in the Values area.
Hi Hans,
That's an awesome effort (thank you). I didnt think about pivot tables (I was too focused on vlookups).
My only question now is how I can take the data provided and turn it into days of the week?
I have manually been collating this data below (very time consuming). This is data from the month of February. Below is an example of what I am trying to achieve. See the below graph - the graph shows the average hourly power usage by day.
| MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY | SUNDAY | |
| 12:30:00 AM | 8.16 | 7.72 | 7.725 | 8 | 7.575 | 7.85 | 7.76 |
| 1:30:00 AM | 7.8 | 7.96 | 7.9 | 7.85 | 7.75 | 7.675 | 7.68 |
| 2:30:00 AM | 7.96 | 8.08 | 7.9 | 8.125 | 7.725 | 7.975 | 7.82 |
| 3:30:00 AM | 8.52 | 8.4 | 7.925 | 7.75 | 8.15 | 8.225 | 8.12 |
| 4:30:00 AM | 12.06 | 12.42 | 15.1 | 19.75 | 17.5 | 7.95 | 7.66 |
| 5:30:00 AM | 21.92 | 29.9 | 34.8 | 24.45 | 22.225 | 7.825 | 7.9 |
| 6:30:00 AM | 28.58 | 34.98 | 40.375 | 28.225 | 25.2 | 8.075 | 8.52 |
| 7:30:00 AM | 29.9 | 33.6 | 34.9 | 29.675 | 29.075 | 7.975 | 11.8 |
| 8:30:00 AM | 34.96 | 36.1 | 36.425 | 31.275 | 29.825 | 9.175 | 11.72 |
| 9:30:00 AM | 37.22 | 34.96 | 35.6 | 31.125 | 29.65 | 9.5 | 10.58 |
| 10:30:00 AM | 36.48 | 34.48 | 33.675 | 30.25 | 28.775 | 10.025 | 9.36 |
| 11:30:00 AM | 37.7 | 36.1 | 34.35 | 33.725 | 31.075 | 9.975 | 9.06 |
| 12:30:00 PM | 39.58 | 35.54 | 35.575 | 40.475 | 34 | 10.825 | 9.22 |
| 1:30:00 PM | 40.16 | 35.78 | 41.475 | 40.525 | 36.4 | 9.575 | 10.56 |
| 2:30:00 PM | 42.88 | 36.4 | 39.15 | 41.3 | 35.525 | 9 | 8.96 |
| 3:30:00 PM | 40.66 | 36.26 | 36.725 | 39.925 | 35.8 | 8.125 | 8.06 |
| 4:30:00 PM | 38.4 | 33.88 | 32.825 | 38.3 | 33.4 | 7.75 | 8 |
| 5:30:00 PM | 28.78 | 28 | 26.175 | 29.85 | 24.65 | 7.25 | 7.16 |
| 6:30:00 PM | 18.86 | 19.74 | 18.6 | 19.125 | 17.425 | 7.85 | 8.42 |
| 7:30:00 PM | 15.4 | 15.12 | 17.65 | 16.225 | 11.4 | 7.425 | 8.28 |
| 8:30:00 PM | 12.36 | 13.44 | 12.45 | 11.15 | 10.5 | 7.95 | 9.02 |
| 9:30:00 PM | 7.92 | 9.46 | 8.75 | 7.8 | 9.575 | 8.075 | 8.32 |
| 10:30:00 PM | 7.8 | 8.14 | 8 | 7.65 | 7.85 | 8.125 | 8.48 |
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.
