+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    09-12-2018
    Location
    Chile
    MS-Off Ver
    2016
    Posts
    2

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

    I have a table that has almost 1.000 substations as columns and each substation has energy prices for every hour for each month (744 values for each substation for January, for example). My objective is to get a monthly average of those prices for each substation by filtering the month I want to check, but of course, I don't think that adding one field by one to a pivot tablewill be the best approach.

    These tables look like this:

    Month Substation 1 ... Substation 999
    201701 35 ... 29
    201701 46 ... 16
    ... ... ... ...
    201712 35 ... 27
    201712 39 ... 100


    Any suggestion or recommendation on how could I approach this problem would be awesome. Thanks!

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,794

    Re: 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.
    Martin

    If my solution has saved you time and/or money, please consider donating to Cancer Research UK or your local equivalent.

    https://www.cancerresearchuk.org/

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    13,779

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

    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/
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  4. #4
    Registered User
    Join Date
    09-12-2018
    Location
    Chile
    MS-Off Ver
    2016
    Posts
    2

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

    Quote Originally Posted by MarvinP View Post
    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.
    Last edited by Dvhez; 09-12-2018 at 06:48 PM.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    13,779

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1