POWER QUERY excercise.xlsx

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ORIGINAL DATA 1 TYPE
2	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17
3							TAIL					BODY
4	Date	Shift	Item	Check Q'ty	Defect Q'ty	Rate	Scrach	Cull	Dent	Color	Short	Long	Big	Small	Color2	Color3	Color4
5	10/2/2021	Day	Blue Pencill	1.000	28	2,80%	6	5		5	3	5	4
6	10/4/2021	Day	Red Pencill	400	17	4,25%	1	5			5	1			5
7	10/4/2021	Day	Black Pencill	3.000	14	0,47%		3			11
8	10/5/2021	Day	Black Pencill	9.449	64	0,68%	9	5	5	6	24	11	1	3
9	10/5/2021	Day	Red Pencill	200	6	3,00%		3			3
10	10/5/2021	Night	Blue Pencill	800	9	1,13%		1	2					6
11	10/5/2021	Night	Black Pencill	2.300	24	1,04%	2			3	16			1	2
12	10/5/2021	Night	Red Pencill	2.200	39	1,77%		1			8				30
13	10/5/2021	Night	Ballpoint pens	800	11	1,38%		1			6				4
14	10/6/2021	Day	Black Pencill	2.000	26	1,30%	1				3			22
15	10/6/2021	Day	Ballpoint pens	4.515	11	0,24%		2			2				3	4
16	10/6/2021	Day	Blue Pencill	1.300	11	0,85%		2			9
17	10/6/2021	Day	Blue Pencill	1.768	51	2,88%			20				1				30
18	10/6/2021	Day	Black Pencill	6.612	49	0,74%	9	3			10			19		8
19	Result 1 : OK
20
21
22
23
24	,Date	,Shift	,Item	,Check Q'ty	,Defect Q'ty	,Rate	Defect Area	Defect Name	Defect Q'ty
25	10/2/2021	Day	Blue Pencill	1000	28	0,028	TAIL	Scrach	6
26	10/2/2021	Day	Blue Pencill	1000	28	0,028	TAIL	Cull	5
27	10/2/2021	Day	Blue Pencill	1000	28	0,028	TAIL	Color	5
28	10/2/2021	Day	Blue Pencill	1000	28	0,028	TAIL	Short	3
29	10/2/2021	Day	Blue Pencill	1000	28	0,028	BODY	Long	5
30	10/2/2021	Day	Blue Pencill	1000	28	0,028	BODY	Big	4
31	10/4/2021	Day	Red Pencill	400	17	0,0425	TAIL	Scrach	1
32	10/4/2021	Day	Red Pencill	400	17	0,0425	TAIL	Cull	5
33	10/4/2021	Day	Red Pencill	400	17	0,0425	TAIL	Short	5
34	10/4/2021	Day	Red Pencill	400	17	0,0425	BODY	Long	1
35	10/4/2021	Day	Red Pencill	400	17	0,0425	BODY	Color2	5
36	10/4/2021	Day	Black Pencill	3000	14	0,004666666667	TAIL	Cull	3
37	10/4/2021	Day	Black Pencill	3000	14	0,004666666667	TAIL	Short	11
38	10/5/2021	Day	Black Pencill	9449	64	0,006773203514	TAIL	Scrach	9
39	10/5/2021	Day	Black Pencill	9449	64	0,006773203514	TAIL	Cull	5
40	10/5/2021	Day	Black Pencill	9449	64	0,006773203514	TAIL	Dent	5
41	10/5/2021	Day	Black Pencill	9449	64	0,006773203514	TAIL	Color	6
42	10/5/2021	Day	Black Pencill	9449	64	0,006773203514	TAIL	Short	24
43	10/5/2021	Day	Black Pencill	9449	64	0,006773203514	BODY	Long	11
44	10/5/2021	Day	Black Pencill	9449	64	0,006773203514	BODY	Big	1
45	10/5/2021	Day	Black Pencill	9449	64	0,006773203514	BODY	Small	3
46	10/5/2021	Day	Red Pencill	200	6	0,03	TAIL	Cull	3
47	10/5/2021	Day	Red Pencill	200	6	0,03	TAIL	Short	3
48	10/5/2021	Night	Blue Pencill	800	9	0,01125	TAIL	Cull	1
49	10/5/2021	Night	Blue Pencill	800	9	0,01125	TAIL	Dent	2
50	10/5/2021	Night	Blue Pencill	800	9	0,01125	BODY	Small	6
51	10/5/2021	Night	Black Pencill	2300	24	0,01043478261	TAIL	Scrach	2
52	10/5/2021	Night	Black Pencill	2300	24	0,01043478261	TAIL	Color	3
53	10/5/2021	Night	Black Pencill	2300	24	0,01043478261	TAIL	Short	16
54	10/5/2021	Night	Black Pencill	2300	24	0,01043478261	BODY	Small	1
55	10/5/2021	Night	Black Pencill	2300	24	0,01043478261	BODY	Color2	2
56	10/5/2021	Night	Red Pencill	2200	39	0,01772727273	TAIL	Cull	1
57	10/5/2021	Night	Red Pencill	2200	39	0,01772727273	TAIL	Short	8
58	10/5/2021	Night	Red Pencill	2200	39	0,01772727273	BODY	Color2	30
59	10/5/2021	Night	Ballpoint pens	800	11	0,01375	TAIL	Cull	1
60	10/5/2021	Night	Ballpoint pens	800	11	0,01375	TAIL	Short	6
61	10/5/2021	Night	Ballpoint pens	800	11	0,01375	BODY	Color2	4
62	10/6/2021	Day	Black Pencill	2000	26	0,013	TAIL	Scrach	1
63	10/6/2021	Day	Black Pencill	2000	26	0,013	TAIL	Short	3
64	10/6/2021	Day	Black Pencill	2000	26	0,013	BODY	Small	22
65	10/6/2021	Day	Ballpoint pens	4515	11	0,002436323367	TAIL	Cull	2
66	10/6/2021	Day	Ballpoint pens	4515	11	0,002436323367	TAIL	Short	2
67	10/6/2021	Day	Ballpoint pens	4515	11	0,002436323367	BODY	Color2	3
68	10/6/2021	Day	Ballpoint pens	4515	11	0,002436323367	BODY	Color3	4
69	10/6/2021	Day	Blue Pencill	1300	11	0,008461538462	TAIL	Cull	2
70	10/6/2021	Day	Blue Pencill	1300	11	0,008461538462	TAIL	Short	9
71	10/6/2021	Day	Blue Pencill	1768	51	0,02884615385	TAIL	Dent	20
72	10/6/2021	Day	Blue Pencill	1768	51	0,02884615385	BODY	Big	1
73	10/6/2021	Day	Blue Pencill	1768	51	0,02884615385	BODY	Color4	30
74	10/6/2021	Day	Black Pencill	6612	49	0,0074107683	TAIL	Scrach	9
75	10/6/2021	Day	Black Pencill	6612	49	0,0074107683	TAIL	Cull	3
76	10/6/2021	Day	Black Pencill	6612	49	0,0074107683	TAIL	Short	10
77	10/6/2021	Day	Black Pencill	6612	49	0,0074107683	BODY	Small	19
78	10/6/2021	Day	Black Pencill	6612	49	0,0074107683	BODY	Color3	8
79
80	ORIGINAL DATA 2 TYPE
81	ORIGINAL DATA 2 TYPE
82	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16
83	Date		10/2/2021	10/4/2021	10/4/2021	10/5/2021	10/5/2021	10/5/2021	10/5/2021	10/5/2021	10/5/2021	10/6/2021	10/6/2021	10/6/2021	10/6/2021	10/6/2021
84	Check Q'ty		1.000	400	3.000	9.449	200	800	2.300	2.200	800	2.000	4.515	1.300	1.768	6.612
85	Defect Q'ty		28	17	14	64	6	9	24	39	11	26	11	11	51	49
86	Rate
87	TAIL	Scrach	6	1		9			2			1				9
88		Cull	5	5	3	5	3	1		1	1		2	2		3
89		Dent				5		2							20
90		Color	5			6			3
91		Short	3	5	11	24	3		16	8	6	3	2	9		10
92	BODY	Long	5	1		11
93		Big	4			1									1
94		Small				3		6	1			22				19
95		Color2		5					2	30	4		3
96		Color3											4			8
97		Color4													30
98	Please help me convert to below result ???
99
100