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).
