Collating tricky data

[KITSONA]

Ok team, I’m rally needing help.

I have one years worth of 1/2hourly electricity usage data. I want to condense it to hourly usage then compare hours of each day of the week in respective months.

I haven’t found a good formula to achieve this. I’m hoping some smart cookie out there will know how to crack the code

[#2]

@KITSONA 

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.

[#3]

@Hans Vogelaar 

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

[#4]

@KITSONA 

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.

 

[#5]

@Hans Vogelaar 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).

[#6]

@Riny_van_Eekelen 

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.

[#7]

Thanks @Hans Vogelaar & @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

[#8]

@KITSONA 

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. 

[#9]

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!!

[#10]

@KITSONA 

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.

 

[#11]

Thanks Hans. I will also be interested to see Riny reply too.

Many thanks

[#12]

@KITSONA Like Hans, my first file averaged the half hourly numbers for each hour. I guess we weren't clear on what you asked for. The attached file contains a revised summary that first sums total KWh for each hour and then averages these hourly totals for each hour of the day. In PQ you can group records on whatever criteria you choose. As long as you have a column in your table to refer to. The advantage of PQ, in my view, is that for data you are dealing with, it's very easy to create new columns by extracting date and or time segments from a date/time column.

For more complex models, where you'd add a Calendar table that creates a date hierarchy that you can then use in pivot tables. So, in summary, with PQ and PP you can achieve everything that you can achieve with regular PT's and much more.

[#13]

Hi @Riny_van_Eekelen thanks for your contribution and showing me different tools I can use.

I have never used PQ before, so may have some learnings to do. 

The reason I am collating this data is I am trying to show what size solar system these people need. So the ideal situation is for me to trade off their usage data with production data. Happy to attach that if you are willing to challenge yourself? 

After that, the best scenario for me is to use what you have provided as a template for what I need to do for other people.

Let me know if you are keen to challenge yourself by overlaying this with generation data?