Pivot Table with monthly data per columns

[pasbleueau]

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?

Regards.

 

[Alex Blakenburg]

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.

 

[pasbleueau]

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?

 

[Alex Blakenburg]

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

 

[pasbleueau]

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.

Aspect	Type	Building	Unit	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20
Energy	Electricity	Factory 1	kWh	178,488	166,416	180,748	178,656	181,436	164,820	197,188
Energy	Electricity	Factory 1	kWh	24,687	27,625	28,673	23,033	22,975	26,763	26,812
Energy	Gas	Factory 2	MMBtu	1,000	1,220	1,210	1,002	909	1,021	1,021
Water	Water	Factory 1	m3	20	30	20	20	10	20	20
Waste	Normal Waste	Factory 1	kg	130	121	198	175	198	165	175

 

[pasbleueau]

Oh ... I just notice that I'm using Office Home & Business 2016, and that Power Pivot is not available.
Aaaaarg.

Any alternative?

 

[Alex Blakenburg]

That's not a show stopper. You don't need the data model for this. So Power Query and standard Pivot will work fine.

 

[pasbleueau]

That's not a show stopper. You don't need the data model for this. So Power Query and standard Pivot will work fine.

Oh great. Then I just need help/guidance on how to "unpivot" ...

 

[Alex Blakenburg]

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.

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

 

[pasbleueau]

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.