Pivot Table with monthly data per columns

pasbleueau

New Member
Joined
Jun 30, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Good day everybody,

New to this forum, first post. I guess this question has been addressed before but I've been unable to find anything in the existing thread. Don't hesitate to re-direct me if necessary.

So my question: I have a table, as per below screenshot, recording some consumption every months. I would like to create a pivot table to analyse this data, and filter them by year, by Aspect, by factory ... in short, a pivot table. However, since the values are recorded per month in the columns, I'm unable to do so.
Would someone have a solution?

1625038645631.png


Regards.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Are you open to using Power Query ?
Its currently in what is considered to be a pivoted format and Power Query will let you "Unpivot" it, so you can use a normal pivot on it.
When you refresh all it will refresh both the PQ and the Pivot.
 
Upvote 0
Are you open to using Power Query ?
Its currently in what is considered to be a pivoted format and Power Query will let you "Unpivot" it, so you can use a normal pivot on it.
When you refresh all it will refresh both the PQ and the Pivot.
Yes, I started to understand that my solution was in the Power Query ...
I'm of course open to it, but I've never used it. A quick explanation on how I should "unpivot" my table?
 
Upvote 0
Are you able to provide an XL2BB sample of you data for me to work on ?
Also have you already set it up as an Excel Table and if so what is the Table name you have used.
The table name gets hard coded in the Query, so please make it a name that means something not just the default Table1
 
Upvote 0
Sorry, due to company restrictions I'm not able to download XL2BB. But I like to understand things by my own, with an example, so you can just copy/paste the table below to an Excel file so I can re-use it afterwards.
The table name is "DataTable".

Thank you very much, very nice to have your help.

AspectTypeBuildingUnitJan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20
EnergyElectricityFactory 1kWh178,488166,416180,748178,656181,436164,820197,188
EnergyElectricityFactory 1kWh24,68727,62528,67323,03322,97526,76326,812
EnergyGasFactory 2MMBtu1,0001,2201,2101,0029091,0211,021
WaterWaterFactory 1m320302020102020
WasteNormal WasteFactory 1kg130121198175198165175
 
Upvote 0
Oh ... I just notice that I'm using Office Home & Business 2016, and that Power Pivot is not available.
Aaaaarg.

Any alternative?
 
Upvote 0
That's not a show stopper. You don't need the data model for this. So Power Query and standard Pivot will work fine.
 
Upvote 0
Ok I will start with giving you to M Code.
  • Click anywhere in your table and
    Data > From Table Range
    This will put you into Power Query
  • Then Home > Advanced Editor
  • Select everything you see in the editor and replace it with the code below
  • Hit Done
  • Change the name of the query on the right hand pane.
    If you decide to load it as a table it will give the output table that same name
  • File > Close and Load To
    • You can either go straight to a Pivot table output
    • OR output it as a table first that you then use as a basis for your pivot
      You have the option of specifying a location or just letting it create a new sheet.
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="DataTable"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Unit", "Building", "Type", "Aspect"}, "Attribute", "Value"),
    #"Added Prefix To Month" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each "01-" & _, type text}}),
    #"Renamed Columns Month" = Table.RenameColumns(#"Added Prefix To Month",{{"Attribute", "Month"}, {"Value", "Amount"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns Month",{{"Aspect", type text}, {"Type", type text}, {"Building", type text}, {"Unit", type text}, {"Month", type date}, {"Amount", type number}})
in
    #"Changed Type"

Interface steps.
  • Click anywhere in your table and
    Data > From Table Range
    This will put you into Power Query
  • Delete the Changed Type step
    This step will hard code the months and you don't want that as the months will change in the future
  • Select the text columns Aspect to Unit
  • Transform > Unpivot Columns > Unpivot Other Columns
  • We need to be able to convert the text mmm-yy to a date so Transform > Format > Add Prefix
    Added "01-" without the quotes
  • Rename the Attribute column to Month AND Value column to Amount
  • Select all columns (Ctrl+A) and Transform > Detect Data type
    I then changed the value column to Decimal not knowing what your full data set looks like
  • Then close and load to same as above
 
Upvote 0
Solution
Waw ... I am very impressed. It works perfectly, and it's very powerful. Waw ... thank you very much Alex.
I just didn't manage to close and load to a pivot table directly, but it's not a big deal.

I will try to play with it and adapt it to my other workbooks. In the end, I will use this Power Query to gather information from several files, I hope I'm gonna be able to find my way.

Thank you again.
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top