Power Query Get Transform the the in Tabular Format

[mmr1]

Hi,

I have a huge data which is converted from another source to excel required to be set in Tabular format with help power query.Headers are already available it should be same as it is, Blank columns and rows to be remove, Data in column which does not have any headers to be create a new header name in the particular that column.

Sample enclosed for reference,

Thanks for the help,

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Period	Batch	Trans Ref	Chq/Db/Cr Name	Description		GRN No	Order No	Quantity	Unit			This Period	Cumulative
2				SUPER TRADING							0.00	0.00		3,445.00	3,445.00
3
4		06-Aug-2020	202008	DELACC	DO/11443		Steel Nails 1.5 Inch - Dq Holland- 1 X 120 ORDNO:DBB444063			DBB444063	100	0.00	Pkt	0.00	625.00
5	Voucher: 07A33
6		06-Aug-2020	202008	DELACC	DO/11443		Steel Nails 2 Inch - Dq Holland- 1 X 120 ORDNO:DBB444063			DBB444063	40	0.00	Pkt	0.00	300.00
7	Voucher: 07A33
8				THUNDER TECHNICAL TRDG CO LLC							0.00	0.00		(0.00)	(0.00)
9
10		09-Jul-2020	202008	DEL1673081	114510		3 WHIP CHECK 1.0 MTRS LONG		0000176767 11/07/2020	DBB441928	3	0.00	Nos	0.00	72.00
11		09-Jul-2020	202008	DEL1673081	114510		2 WHIP CHECK 0.5 MTRS LONG		0000176767 11/07/2020	DBB441928	40	0.00	Nos	0.00	600.00
12		09-Jul-2020	202008	DELACC	73304		Automatic Reversal - 3 WHIP CHECK 1.0 MTRS LONG ORDNO:DBB441928			DBB441928	-3	0.00	Nos	0.00	(72.00)
13	Voucher: 07A32
14		09-Jul-2020	202008	DELACC	73304		Automatic Reversal - 2 WHIP CHECK 0.5 MTRS LONG ORDNO:DBB441928			DBB441928	-40	0.00	Nos	0.00	(600.00)
15	Voucher: 07A32
16				ULTIMATE TRADING CO LLC							0.00	0.00		3,130.00	3,130.00
17
18		16-Aug-2020	202008	DELACC	DO-52735		ESPA PRISMA WATER PUMP 25/04 M SPAIN 1PH 2HP 1" X 1" WITH ESPA PERSSURE KIT ORDNO:DBB444546			DBB444546	2	0.00	Nos	0.00	3,130.00
19	Voucher: 07A33
20	575 - Other PandGS										0.00	0.00		7,400.00	12,242.20
21
22	57520 - Safety and Cleaning										0.00	0.00		7,400.00	12,242.20
23
24	57520021 - Safety Equipment and Signage										0.00	0.00		7,400.00	12,242.20
25									b fwd	3505					4,842.20
26
27				SAFETY EQUIP. TRADING CO.							0.00	0.00		0.00	0.00
28
29		16-Jul-2020	202008	DELACC	DT/TAS/112		Automatic Reversal - WELDER SAFETY SHOE - SIZE-40, CHALLENGER BRAND ORDNO:DBB443011			DBB443011	-1	0.00	Pr	0.00	(120.00)
30	Voucher: 07A32
31		16-Jul-2020	202008	DELACC	DT/TAS/112		Automatic Reversal - 100 COTTON FULL SLEEVE LIGHT BLUE COLOUR WELDER COVERALL TOUGH EXCEL- Size- M ORDNO:DBB443011			DBB443011	-2	0.00	Nos	0.00	(80.00)
32	Voucher: 07A32
Sheet1

 

[sandy666]

you've solution in your post

 

[mmr1]

Hi,

Thanks, for the response,

I am not much familiar with Power Query, However i try to workout what ever i have an idea about it, here is require a help for Column A, i want to shift the texts as same manner what other columns have, so then i can filter out blank cells easily.


Thanks,

 

[sandy666]

post the result of your work

it seems you've 4 tables in one

 

[sandy666]

is this a blank column or not?

Column2
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

 

[mmr1]

Yes, that is blank column

 

[sandy666]

I wonder why column Date doesn't contain dates?

so far but the rest is up to you because I don't know data is linked vertically or horizontally

Date	Period	Batch	Trans Ref	Chq/Db/Cr Name	Column1	Order No	Quantity	Unit	Column3	This Period	Cumulative
	06/08/2020	202008	DELACC	DO/11443	Steel Nails 1.5 Inch - Dq Holland- 1 X 120 ORDNO:DBB444063		DBB444063	100	Pkt	0	625
Voucher: 07A33
	06/08/2020	202008	DELACC	DO/11443	Steel Nails 2 Inch - Dq Holland- 1 X 120 ORDNO:DBB444063		DBB444063	40	Pkt	0	300
Voucher: 07A33
	09/07/2020	202008	DEL1673081	114510	3 WHIP CHECK 1.0 MTRS LONG	0000176767 11/07/2020	DBB441928	3	Nos	0	72
	09/07/2020	202008	DEL1673081	114510	2 WHIP CHECK 0.5 MTRS LONG	0000176767 11/07/2020	DBB441928	40	Nos	0	600
	09/07/2020	202008	DELACC	73304	Automatic Reversal - 3 WHIP CHECK 1.0 MTRS LONG ORDNO:DBB441928		DBB441928	-3	Nos	0	-72
Voucher: 07A32
	09/07/2020	202008	DELACC	73304	Automatic Reversal - 2 WHIP CHECK 0.5 MTRS LONG ORDNO:DBB441928		DBB441928	-40	Nos	0	-600
Voucher: 07A32
	16/08/2020	202008	DELACC	DO-52735	ESPA PRISMA WATER PUMP 25/04 M SPAIN 1PH 2HP 1" X 1" WITH ESPA PERSSURE KIT ORDNO:DBB444546		DBB444546	2	Nos	0	3130
Voucher: 07A33
575 - Other PandGS								0		7400	12242.2
57520 - Safety and Cleaning								0		7400	12242.2
57520021 - Safety Equipment and Signage								0		7400	12242.2
						b fwd	3505				4842.2
	16/07/2020	202008	DELACC	DT/TAS/112	Automatic Reversal - WELDER SAFETY SHOE - SIZE-40, CHALLENGER BRAND ORDNO:DBB443011		DBB443011	-1	Pr	0	-120
Voucher: 07A32
	16/07/2020	202008	DELACC	DT/TAS/112	Automatic Reversal - 100 COTTON FULL SLEEVE LIGHT BLUE COLOUR WELDER COVERALL TOUGH EXCEL- Size- M ORDNO:DBB443011		DBB443011	-2	Nos	0	-80
Voucher: 07A32

 

[sandy666]

Date	Period	Batch	Trans Ref	Chq/Db/Cr Name	Column1	Order No	Quantity	Unit	Column3	This Period	Cumulative
Voucher: 07A33	06/08/2020	202008	DELACC	DO/11443	Steel Nails 1.5 Inch - Dq Holland- 1 X 120 ORDNO:DBB444063		DBB444063	100	Pkt	0	625
Voucher: 07A33	06/08/2020	202008	DELACC	DO/11443	Steel Nails 2 Inch - Dq Holland- 1 X 120 ORDNO:DBB444063		DBB444063	40	Pkt	0	300
Voucher: 07A32	09/07/2020	202008	DEL1673081	114510	3 WHIP CHECK 1.0 MTRS LONG	0000176767 11/07/2020	DBB441928	3	Nos	0	72
Voucher: 07A32	09/07/2020	202008	DEL1673081	114510	2 WHIP CHECK 0.5 MTRS LONG	0000176767 11/07/2020	DBB441928	40	Nos	0	600
Voucher: 07A32	09/07/2020	202008	DELACC	73304	Automatic Reversal - 3 WHIP CHECK 1.0 MTRS LONG ORDNO:DBB441928		DBB441928	-3	Nos	0	-72
Voucher: 07A32	09/07/2020	202008	DELACC	73304	Automatic Reversal - 2 WHIP CHECK 0.5 MTRS LONG ORDNO:DBB441928		DBB441928	-40	Nos	0	-600
Voucher: 07A33	16/08/2020	202008	DELACC	DO-52735	ESPA PRISMA WATER PUMP 25/04 M SPAIN 1PH 2HP 1" X 1" WITH ESPA PERSSURE KIT ORDNO:DBB444546		DBB444546	2	Nos	0	3130
575 - Other PandGS								0		7400	12242.2
57520 - Safety and Cleaning								0		7400	12242.2
57520021 - Safety Equipment and Signage								0		7400	12242.2
Voucher: 07A32	16/07/2020	202008	DELACC	DT/TAS/112	Automatic Reversal - WELDER SAFETY SHOE - SIZE-40, CHALLENGER BRAND ORDNO:DBB443011		DBB443011	-1	Pr	0	-120
Voucher: 07A32	16/07/2020	202008	DELACC	DT/TAS/112	Automatic Reversal - 100 COTTON FULL SLEEVE LIGHT BLUE COLOUR WELDER COVERALL TOUGH EXCEL- Size- M ORDNO:DBB443011		DBB443011	-2	Nos	0	-80

 

[mmr1]

here is i have work out

Book1
	A	B	C	D	E	F	G	H
1	Date	Batch	Trans Ref	Chq/Db/Cr Name	Description	Quantity	This Period	Cumulative
2	06-08-2020 0:00	202008	CBC2790806	CV27003	FINAL SETTLEMENT - DBB29396 - KAILASH VISHVAKARMA - CHQ. NO. 620251	0	0	174
3	18-08-2020 0:00	202008	CBC1256081	CV27104	PAID LEAVE SETTLEMENT-DBB29634-AMRO SAYED SOLIMAN ABOUELGHIT-CHQ-NO-620329	0	0	569
4	19-08-2020 0:00	202008	CBC2790819	CV27119	FINAL SETTLEMENT - DBB16673 - JAGDISH YADAV - CHQ. NO. 620343	0	0	379
5	19-08-2020 0:00	202008	CBC1687081	CV27123	FINAL SETTLEMENT-DBB13433-NALLELA MURALI-CHQ. NO.620346	0	0	926
6			Ishaq			0	2644	2644
7	27-08-2020 0:00	202008	CBC1687082	CV27190	PAID LEAVE SETTLEMENT-DBB30773-ISHAQ-CHQ. NO. 620395	0	0	2644
8			MINAT GUL			0	1712	1712
9	18-08-2020 0:00	202008	CBC1256081	CV27103	PAID LEAVE SETTLEMENT-DBB33682-MINAT GUL-CHQ-NO-620328	0	0	1712
10			Sikander Hayat			0	536	536
11	18-08-2020 0:00	202008	CBC1687081	CV27106	FINAL SETTLEMENT-DBB28132-SIKANDER HAYAT-CHQ. NO. 620325	0	0	536
12	20-08-2020 0:00	202008	CBC1256082	CV27126	FINAL SETTLEMENT-DBB35206-RANKALU LACHHAMAN REDDY-CHQ-NO-620357	0	0	349
13	20-08-2020 0:00	202008	CBC1256082	CV27127	FINAL SETTLEMENT-DBB35205-RANKALU BIJAYA REDDY-CHQ-NO-620356	0	0	563
14	29-08-2020 0:00	202008	CBC1687082	CV27185	FINAL SETTLEMENT-DBB31606-FAISAL SHAHZAD-CHQ. NO.620393	0	0	930
15			AMJAD ABBASI			0	475	475
Sheet1

 

[mmr1]

Thanks for your kind consideration, i have done it by looking your above posted sample