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]

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.

 

[pasbleueau]

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

 

[Alex Blakenburg]

Can you post some sample data xl2bb if possible

 

[pasbleueau]

Can you post some sample data xl2bb if possible

Here it is, an example:

Template MrExcel.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
3	Code	Aspect	Category	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19
4	A	Sexe	Male	922	280	712	936	115	837	679	964	803	953	404	670	417	892	977	423	966	81	769	72	74	949	344	958	643	306
5	A	Sexe	Female	485	181	258	701	218	310	228	495	957	457	597	776	352	107	853	33	49	835	620	129	459	651	124	491	252	245
6	A	Age	33	302	443	973	521	993	168	289	36	662	928	75	949	362	319	532	54	813	456	129	766	527	104	356	42	828	512
7	B	Nationality	French	946	156	51	721	805	796	856	45	321	838	549	393	17	172	177	553	934	337	570	718	288	200	439	576	482	10
Sheet1

And the second table, which is on another sheet:

Template MrExcel.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
3	Code	Brand	Model	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18
4	C	Toyota	Yaris	4	7	8	1	4	2	9	1	2	9	2	10	7	4	2	2	10	2	10
5	A	Audi	A3	7	6	2	2	5	2	9	8	5	3	2	2	1	2	7	7	4	7	4
6	B	Audit	A3	7	6	2	9	5	6	9	8	5	3	1	2	9	2	7	7	4	7	4
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]

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]

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]

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.