How to include more than one field at once in a PivotTable

Maybe you could generate a unique list of months and then use the AVERAGEIFS function to get the desired numbers.

Hi Dvhez and welcome to the forum,

I'd convert your "Crosstab Table" to a 3 column table using Power Query UnPivot. Then you would have Month, Substation Number and Value. Then I'd use a Pivot Table to get your answer.

https://www.myonlinetraininghub.com/power-query-unpivot or
https://www.excelcampus.com/tables/unpivot-power-query/

Originally Posted by MarvinP

Hi Dvhez and welcome to the forum,

I'd convert your "Crosstab Table" to a 3 column table using Power Query UnPivot. Then you would have Month, Substation Number and Value. Then I'd use a Pivot Table to get your answer.

Thanks for your answer!.However, by doing that I can cover only 2 months because I reach the row limit (bit more than 1 million). Is there a way to calculate the monthly average BEFORE creating a table from the Query Editor?

EDIT: I was causing that problem because I created a table instead of working with the resulting connection using Power Pivot. Again, thanks for your help and time.

Hi,

I hope you have an answer to this question by now. If you have too many rows after an UnPivot operation, you might consider filtering the rows (or columns) in Power Query before loading the data back into Excel, which has the million rows limit. I don't think there is a limit on the number of rows in the Power Query Editor.