Transpose or unpivot (?) with conditions

[arake]

Hi!

Can you please help me out with a transpose/unpivot problem ? ?
On top of being a total newb in power query, English is not my native language, so i'll try and do my best to be as clear as possible.

So, I've gone this far in "cleaning" an extract i've got from a survey about drinks purchasing habits. Problem is it all came into columns like that :

An ID corresponds to a person who has bought a particular category of drinks (Beers, fruit juices, etc.). For each category of drinks the person purchased, he/she is asked to detail the brand as well as the format, the packaging it came in, and so on.

From that, i'm trying to transpose the items in the "Custom" column into their own column, while keeping the corresponding data in the "Valeur" column, like so :

Can you help me out with that please ? What steps would you take to do it ?

Thank you very much for your help

 

[Alex Blakenburg]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Below is your sample data as an XL2BB it might help get you a response.

20210605 PQ Transpose.xlsx
	A	B	C	D
1	ID	Custom	Valeur
2	1	CATEGORY	Aucune
3	2	CATEGORY	PUR JUS DE FRUITS
4	2	BRAND	ORO
5	2	FORMAT	BOUTEILLE SOCL
6	2	PACKAGING	1x
7	2	ON SALE	Non
8	2	CONSUMER	Pour vous
9	2	PLACE	Discounter (Leader Price, Discount)
10	2	CATEGORY	COLAS
11	2	BRAND	COCA COLA
12	2	FORMAT	BOITE 25CL
13	2	PACKAGING	1x
14	2	ON SALE	non
15	2	CONSUMER	Pour vous
16	2	PLACE	Discounter (Leader Price. Discount)
17	3	CATEGORY	Aucune
18	4	CATEGORY	BIERE
19	4	BRAND	NUMBER ONE
20	4	FORMAT	BOITE 33CL
21	4	PACKAGING	12x
22	4	ON SALE	Non
23	4	CONSUMER	Pour vous
24	4	PLACE	Petits magasins alimentaires
25	4	BRAND	NUMBER ONE
26	4	FORMAT	BOUTEILLE 33CL
27	4	PACKAGING	8x
28	4	ON SALE	Non
29	4	CONSUMER	Pour vous
30	4	PLACE	Petits magasins alimentaires
31	4	BRAND	NUMBER ONE
32	4	FORMAT	BOITE 50cl
33	4	PACKAGING	24x
34	4	ON SALE	Non
35	4	CONSUMER	Pour vous
36	4	PLACE	Petits magasins alimentaires
37
Data

 

[arake]

Thanks very much for that!