Pivot Table Grouping

[marshalltj67]

Good Afternoon All,

I currently have two pivot tables, one has solar energy production data for a day split in hours, i.e., "Monday, 7/01/2024 - 12:00am - 1:00am" and the other has electricity usage data for a day split in 15 minute increments, i.e., "2024-07-01 00:00:00 to 2024-07-01 00:15:00", "2024-07-01 00:15:00 to 2024-07-01 00:30:00", and so on and so fourth. 

I am trying to group the pivot table that is split in 15 minute increments by every 4 rows to match the solar energy production data for one hour increments but I am having trouble working the pivot table to group automatically.

I tried creating a calculated field using the following formula but had no luck: 

SUM(OFFSET("STARTING ROW",(ROW()-ROW("COLUMN START"))*4,0,4,1))

Is there an easier way to compute this so when I update the data it is automatically group by every 4 rows? It is a bunch of data (goes from 0000 to 2345 in 15 minutes) so grouping manually is not ideal. 

Please see the attached picture for reference and I can provide the excel sheet if needed.

Thank you!

Marshall

[#2]

Hi @marshalltj67 

With Get & Transform aka Power Query (Excel >/= 2016 on Windows or 365 on Mac), grouped data in SourceTable every 4 rows to create the PERIOD field. The output of the query is used as data source for the PivotTable

If you have challenge adapting the query to your actual table share a sample representative workbook please

[#3]

@marshalltj67 

I would transform the data a bit in Power Query.

For the solar production data, create separate columns for the Date and the Time. Then, from the Time column, extract only the Hour portion based a 24-hour clock. For the time period 1pm to 1:59pm that would be 13. Do something similar for the Usage table though now with 4 entries per hour. 

Append the two tables (also in Power Query), load back to Excel or the Data Model and create a pivot table with date and hour in the Row, Type (Production/Usage) in the Column and kWh (I presume) in the value field as a Sum.

The pivot table will automatically sum the four (15-minute) rows per hour from Usage into one number for the entire hour. No need for complicated calculated fields.

If you could upload or share a file (Onedrive, Dropbox or similar) with the production and usage data, I or someone else could more easily demonstrate how that would work.