Data Transformation in Power Query

[srehman]

Hi Team,

I need to transform below data into desired result any idea

! NBN Slotting Temp query-Table PQ.xlsx
	L	M	N	O	P	Q	R
1	Required Result in Power Query
2	Aisle	00	10	20	30	40	50
3	EA	A-L_59	B-L_55	C-L_64	Dead_48	Dead_40	Dead_3
4	EB	A-L_28	B-L_22	C-L_68	Dead_59	Dead_37	Dead_10
5	EC	A-L_45	B-L_34	C-L_70	Dead_70	Dead_45	Dead_6
Sheet2

! NBN Slotting Temp query-Table PQ.xlsx
	A	B	C	D	E	F	G
1	Aisle	00	10	20	30	40	50
2	EA	A-L_59	A-L	A-L	A-L	A-L	A-L
3	EA	B-L	B-L_55	B-L	B-L	B-L	B-L
4	EA	C-L	C-L	C-L_64	C-L	C-L	C-L
5	EA	Dead	Dead	Dead	Dead_48	Dead_40	Dead_3
6	EB	A-L_28	A-L	A-L	A-L	A-L	A-L
7	EB	B-L	B-L_22	B-L	B-L	B-L	B-L
8	EB	C-L	C-L	C-L_68	C-L	C-L	C-L
9	EB	Dead	Dead	Dead	Dead_59	Dead_37	Dead_10
10	EC	A-L_45	A-L	A-L	A-L	A-L	A-L
11	EC	B-L	B-L_34	B-L	B-L	B-L	B-L
12	EC	C-L	C-L	C-L_70	C-L	C-L	C-L
13	EC	Dead	Dead	Dead	Dead_70	Dead_45	Dead_6
14	ED	A-L_80	A-L	A-L	A-L	A-L	A-L
15	ED	B-L	B-L_82	B-L	B-L	B-L	B-L
16	ED	C-L	C-L	C-L_78	C-L	C-L	C-L
17	ED	Dead	Dead	Dead	Dead_56	Dead_55	Dead_1
18	EE	A-L_77	A-L	A-L	A-L	A-L	A-L
19	EE	B-L	B-L_48	B-L	B-L	B-L	B-L
20	EE	C-L	C-L	C-L_62	C-L	C-L	C-L
21	EE	Dead	Dead	Dead	Dead_66	Dead_37	Dead_2
22	EF	A-L_61	A-L	A-L	A-L	A-L	A-L
23	EF	B-L	B-L_66	B-L	B-L	B-L	B-L
24	EF	C-L	C-L	C-L_66	C-L	C-L	C-L
25	EF	Dead	Dead	Dead	Dead_64	Dead_39	Dead_10
26	EG	A-L	A-L_72	A-L	A-L	A-L	A-L
27	EG	AA-L_74	AA-L	AA-L	AA-L	AA-L	AA-L
28	EG	B-L	B-L	B-L_70	B-L	B-L	B-L
29	EG	C-L	C-L	C-L	C-L_66	C-L	C-L
30	EG	Dead	Dead	Dead	Dead	Dead_62	Dead_3
31	EH	A-L	A-L_257	A-L	A-L	A-L	A-L
32	EH	AA-L_197	AA-L	AA-L	AA-L	AA-L	AA-L
33	EH	B-L	B-L	B-L_84	B-L	B-L	B-L
34	EH	C-L	C-L	C-L	C-L_55	C-L	C-L
35	EH	Dead	Dead	Dead	Dead	Dead_54	Dead_1
36	EI	A-L	A-L_78	A-L	A-L	A-L	A-L
37	EI	AA-L_87	AA-L	AA-L	AA-L	AA-L	AA-L
38	EI	B-L	B-L	B-L_69	B-L	B-L	B-L
39	EI	C-L	C-L	C-L	C-L_68	C-L	C-L
40	EI	Dead	Dead	Dead	Dead	Dead_49	Dead
41	EJ	A-L	A-L_82	A-L	A-L	A-L	A-L
42	EJ	AA-L_96	AA-L	AA-L	AA-L	AA-L	AA-L
43	EJ	B-L	B-L	B-L_69	B-L	B-L	B-L
44	EJ	C-L	C-L	C-L	C-L_69	C-L	C-L
45	EJ	Dead	Dead	Dead	Dead	Dead_58	Dead
46	EK	A-L	A-L_365	A-L	A-L	A-L	A-L
47	EK	AA-L_318	AA-L	AA-L	AA-L	AA-L	AA-L
48	EK	B-L	B-L	B-L_123	B-L	B-L	B-L
49	EK	C-L	C-L	C-L	C-L_128	C-L	C-L
50	EK	Dead	Dead	Dead	Dead	Dead_117	Dead_62
51	EL	A-L	A-L	A-L_73	A-L	A-L	A-L
52	EL	AA-L	AA-L_85	AA-L	AA-L	AA-L	AA-L
53	EL	AAA-L_103	AAA-L	AAA-L	AAA-L	AAA-L	AAA-L
54	EL	B-L	B-L	B-L	B-L_77	B-L	B-L
55	EL	C-L	C-L	C-L	C-L	C-L_62	C-L
56	EL	Dead	Dead	Dead	Dead	Dead	Dead_17
57	EM	A-L	A-L	A-L_58	A-L	A-L	A-L
58	EM	AA-L	AA-L_65	AA-L	AA-L	AA-L	AA-L
59	EM	B-L	B-L	B-L	B-L_51	B-L	B-L
60	EM	C-L	C-L	C-L	C-L	C-L_45	C-L
61	EM	Dead	Dead	Dead	Dead	Dead	Dead_1
62	EN	A-L	A-L	A-L_50	A-L	A-L	A-L
63	EN	AA-L	AA-L_4	AA-L	AA-L	AA-L	AA-L
64	EN	B-L	B-L	B-L	B-L_37	B-L	B-L
65	EN	C-L	C-L	C-L	C-L	C-L_40	C-L
66	EN	Dead	Dead	Dead	Dead	Dead	Dead_3
67	EO	A-L	A-L	A-L_73	A-L	A-L	A-L
68	EO	AA-L	AA-L_171	AA-L	AA-L	AA-L	AA-L
69	EO	AAA-L_300	AAA-L	AAA-L	AAA-L	AAA-L	AAA-L
70	EO	B-L	B-L	B-L	B-L_62	B-L	B-L
71	EO	C-L	C-L	C-L	C-L	C-L_65	C-L
72	EO	Dead	Dead	Dead	Dead	Dead	Dead_20
73	EP	A-L	A-L	A-L_19	A-L	A-L	A-L
74	EP	AA-L	AA-L_6	AA-L	AA-L	AA-L	AA-L
75	EP	AAA-L_40	AAA-L	AAA-L	AAA-L	AAA-L	AAA-L
76	EP	B-L	B-L	B-L	B-L_29	B-L	B-L
77	EP	C-L	C-L	C-L	C-L	C-L_17	C-L
78	EP	Dead	Dead	Dead	Dead	Dead	Dead_7
Sheet2

 

[srehman]

I was trying keep trying finally
Got Results i need to perform unPivot & Pivot working 100 %



let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Aisle] = "EA" or [Aisle] = "EB" or [Aisle] = "EC" or [Aisle] = "ED" or [Aisle] = "EE" or [Aisle] = "EF" or [Aisle] = "EG" or [Aisle] = "EH" or [Aisle] = "EI" or [Aisle] = "EJ" or [Aisle] = "EK" or [Aisle] = "EL" or [Aisle] = "EM" or [Aisle] = "EN" or [Aisle] = "EO" or [Aisle] = "EP")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Grand Total"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Aisle", "Levels"}, "Attribute", "Value"),
#"Inserted Merged Column" = Table.AddColumn(#"Unpivoted Other Columns", "Merged", each Text.Combine({[Attribute], Text.From([Value], "en-AU")}, "_"), type text),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"Attribute", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Levels]), "Levels", "Merged")
in
#"Pivoted Column"