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.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
Your most welcome. Thank you for the feedback.

PS: If you have done a close and load, close and load "to" will be greyed out when you are on the Power Query side.
If you want to change how you load it, then when you are on the Excel side,
right click on the query in the Queries and Connections navigation pane on the righthand side > you will see Close and Load to about half way down.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

pasbleueau

New Member
Joined
Jun 30, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hi Alex. I come back on this trend because I would like to amend my file, with a new feature.
What you explained to me is still working great. However, now I would like to add another table (from another sheet) to the database. I explain:
1) Previously, you show me how to unpivot my table (let's call it table A).
2) The result is a new table in PowerQuery, with the data properly formated.
3) Now, I would like to do the same with another table (table B). But I would like the result of this unpivoting, to appear on the same formated table as point number 2).

I guess that the solution is to copy/paste my table A and B into the same table and then to unpivot this one. But I'm not sure how to do this in PowerQuery. To take note, the table A and B are not having the same column name (but in the end, column E is matching with column E of the other sheets (it's just for user understanding purpose, but the type of data are the same in both tables, just the title of the column that change).

Could you help?

Regards
 

pasbleueau

New Member
Joined
Jun 30, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Can you post some sample data xl2bb if possible
Here it is, an example:

Template MrExcel.xlsx
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
3CodeAspectCategoryJan-17Feb-17Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19
4ASexeMale922280712936115837679964803953404670417892977423966817697274949344958643306
5ASexeFemale4851812587012183102284959574575977763521078533349835620129459651124491252245
6AAge3330244397352199316828936662928759493623195325481345612976652710435642828512
7BNationalityFrench94615651721805796856453218385493931717217755393433757071828820043957648210
Sheet1


And the second table, which is on another sheet:

Template MrExcel.xlsx
BCDEFGHIJKLMNOPQRSTUVW
3CodeBrandModelJan-17Feb-17Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18
4CToyotaYaris4781429129210742210210
5AAudiA37622529853221277474
6BAuditA37629569853129277474
Sheet2



The idea is first to merge those 2 tables. And then, to unpivot them, in order to later create a PivotTable to analyse the data.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Neither the unpivoting or merging is hard to do but we would need to discuss what output you are actually wanting to get.
This will impact how we go about it.

eg While it makes sense to use the Code in the name level file to get the get the car details.
The values in the car table won't make sense in that context.
So I am not sure what unpivoting the Car table adds to the equation.

My thoughts would be merge the Car details with the Name table so your Name table has all the details and unpivot the merged table.
You can actually do the merge either before or after the unpivot.

Perhaps mock up and end result and see what you would actually use the car detail values for eg
Code A appears 3 times in the name table. In Jan the car table Code A has a 7 against it.
What are you going to do with a 7 that repeats on each of the Name rows ?
 

pasbleueau

New Member
Joined
Jun 30, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
I'm sorry Alex, what I sent was just an example of my tables, with fake values. I cannot share my original file for confidentiality purpose.
I agree with you that it doesn't make sense, and I guess it's harder for you without the context.
But I will work it out to fit to my original file. What I need is just the step on how to merge those 2 tables ... and have both of them unpivoted, on the same final table.
The result I'm looking for is the same as our previous discussion ... a simple unpivoted table, that then allow me to use the PivotTable function of Excel.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
If you want them linked on Code and Month. Then load each table separately as a connection only and follow the same steps as for the previous example for each table.
Then do a merge query linking (joining) the 2 tables on Code and Month.
 

Forum statistics

Threads
1,147,498
Messages
5,741,504
Members
423,663
Latest member
kaveh87rsh

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
Top