Power Query Get Transform the the in Tabular Format

mmr1

Board Regular
Joined
Aug 25, 2020
Messages
51
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNO
1DatePeriodBatchTrans RefChq/Db/Cr NameDescriptionGRN NoOrder NoQuantityUnitThis PeriodCumulative
2SUPER TRADING0.000.003,445.003,445.00
3
406-Aug-2020202008DELACCDO/11443Steel Nails 1.5 Inch - Dq Holland- 1 X 120 ORDNO:DBB444063DBB4440631000.00Pkt0.00625.00
5Voucher: 07A33
606-Aug-2020202008DELACCDO/11443Steel Nails 2 Inch - Dq Holland- 1 X 120 ORDNO:DBB444063DBB444063400.00Pkt0.00300.00
7Voucher: 07A33
8THUNDER TECHNICAL TRDG CO LLC0.000.00(0.00)(0.00)
9
1009-Jul-2020202008DEL16730811145103 WHIP CHECK 1.0 MTRS LONG0000176767 11/07/2020DBB44192830.00Nos0.0072.00
1109-Jul-2020202008DEL16730811145102 WHIP CHECK 0.5 MTRS LONG0000176767 11/07/2020DBB441928400.00Nos0.00600.00
1209-Jul-2020202008DELACC73304Automatic Reversal - 3 WHIP CHECK 1.0 MTRS LONG ORDNO:DBB441928DBB441928-30.00Nos0.00(72.00)
13Voucher: 07A32
1409-Jul-2020202008DELACC73304Automatic Reversal - 2 WHIP CHECK 0.5 MTRS LONG ORDNO:DBB441928DBB441928-400.00Nos0.00(600.00)
15Voucher: 07A32
16ULTIMATE TRADING CO LLC0.000.003,130.003,130.00
17
1816-Aug-2020202008DELACCDO-52735ESPA PRISMA WATER PUMP 25/04 M SPAIN 1PH 2HP 1" X 1" WITH ESPA PERSSURE KIT ORDNO:DBB444546DBB44454620.00Nos0.003,130.00
19Voucher: 07A33
20575 - Other PandGS0.000.007,400.0012,242.20
21
2257520 - Safety and Cleaning0.000.007,400.0012,242.20
23
2457520021 - Safety Equipment and Signage0.000.007,400.0012,242.20
25b fwd35054,842.20
26
27SAFETY EQUIP. TRADING CO.0.000.000.000.00
28
2916-Jul-2020202008DELACCDT/TAS/112Automatic Reversal - WELDER SAFETY SHOE - SIZE-40, CHALLENGER BRAND ORDNO:DBB443011DBB443011-10.00Pr0.00(120.00)
30Voucher: 07A32
3116-Jul-2020202008DELACCDT/TAS/112Automatic Reversal - 100 COTTON FULL SLEEVE LIGHT BLUE COLOUR WELDER COVERALL TOUGH EXCEL- Size- M ORDNO:DBB443011DBB443011-20.00Nos0.00(80.00)
32Voucher: 07A32
Sheet1
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

mmr1

Board Regular
Joined
Aug 25, 2020
Messages
51
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,132
post the result of your work

it seems you've 4 tables in one
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,132

ADVERTISEMENT

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

Board Regular
Joined
Aug 25, 2020
Messages
51
Office Version
  1. 365
Platform
  1. Windows
Yes, that is blank column
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,132

ADVERTISEMENT

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
DatePeriodBatchTrans RefChq/Db/Cr NameColumn1Order NoQuantityUnitColumn3This PeriodCumulative
06/08/2020202008DELACCDO/11443Steel Nails 1.5 Inch - Dq Holland- 1 X 120 ORDNO:DBB444063DBB444063100Pkt0625
Voucher: 07A33
06/08/2020202008DELACCDO/11443Steel Nails 2 Inch - Dq Holland- 1 X 120 ORDNO:DBB444063DBB44406340Pkt0300
Voucher: 07A33
09/07/2020202008DEL16730811145103 WHIP CHECK 1.0 MTRS LONG0000176767 11/07/2020DBB4419283Nos072
09/07/2020202008DEL16730811145102 WHIP CHECK 0.5 MTRS LONG0000176767 11/07/2020DBB44192840Nos0600
09/07/2020202008DELACC73304Automatic Reversal - 3 WHIP CHECK 1.0 MTRS LONG ORDNO:DBB441928DBB441928-3Nos0-72
Voucher: 07A32
09/07/2020202008DELACC73304Automatic Reversal - 2 WHIP CHECK 0.5 MTRS LONG ORDNO:DBB441928DBB441928-40Nos0-600
Voucher: 07A32
16/08/2020202008DELACCDO-52735ESPA PRISMA WATER PUMP 25/04 M SPAIN 1PH 2HP 1" X 1" WITH ESPA PERSSURE KIT ORDNO:DBB444546DBB4445462Nos03130
Voucher: 07A33
575 - Other PandGS0740012242.2
57520 - Safety and Cleaning0740012242.2
57520021 - Safety Equipment and Signage0740012242.2
b fwd35054842.2
16/07/2020202008DELACCDT/TAS/112Automatic Reversal - WELDER SAFETY SHOE - SIZE-40, CHALLENGER BRAND ORDNO:DBB443011DBB443011-1Pr0-120
Voucher: 07A32
16/07/2020202008DELACCDT/TAS/112Automatic Reversal - 100 COTTON FULL SLEEVE LIGHT BLUE COLOUR WELDER COVERALL TOUGH EXCEL- Size- M ORDNO:DBB443011DBB443011-2Nos0-80
Voucher: 07A32
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,132
DatePeriodBatchTrans RefChq/Db/Cr NameColumn1Order NoQuantityUnitColumn3This PeriodCumulative
Voucher: 07A3306/08/2020202008DELACCDO/11443Steel Nails 1.5 Inch - Dq Holland- 1 X 120 ORDNO:DBB444063DBB444063100Pkt0625
Voucher: 07A3306/08/2020202008DELACCDO/11443Steel Nails 2 Inch - Dq Holland- 1 X 120 ORDNO:DBB444063DBB44406340Pkt0300
Voucher: 07A3209/07/2020202008DEL16730811145103 WHIP CHECK 1.0 MTRS LONG0000176767 11/07/2020DBB4419283Nos072
Voucher: 07A3209/07/2020202008DEL16730811145102 WHIP CHECK 0.5 MTRS LONG0000176767 11/07/2020DBB44192840Nos0600
Voucher: 07A3209/07/2020202008DELACC73304Automatic Reversal - 3 WHIP CHECK 1.0 MTRS LONG ORDNO:DBB441928DBB441928-3Nos0-72
Voucher: 07A3209/07/2020202008DELACC73304Automatic Reversal - 2 WHIP CHECK 0.5 MTRS LONG ORDNO:DBB441928DBB441928-40Nos0-600
Voucher: 07A3316/08/2020202008DELACCDO-52735ESPA PRISMA WATER PUMP 25/04 M SPAIN 1PH 2HP 1" X 1" WITH ESPA PERSSURE KIT ORDNO:DBB444546DBB4445462Nos03130
575 - Other PandGS0740012242.2
57520 - Safety and Cleaning0740012242.2
57520021 - Safety Equipment and Signage0740012242.2
Voucher: 07A3216/07/2020202008DELACCDT/TAS/112Automatic Reversal - WELDER SAFETY SHOE - SIZE-40, CHALLENGER BRAND ORDNO:DBB443011DBB443011-1Pr0-120
Voucher: 07A3216/07/2020202008DELACCDT/TAS/112Automatic Reversal - 100 COTTON FULL SLEEVE LIGHT BLUE COLOUR WELDER COVERALL TOUGH EXCEL- Size- M ORDNO:DBB443011DBB443011-2Nos0-80
 

mmr1

Board Regular
Joined
Aug 25, 2020
Messages
51
Office Version
  1. 365
Platform
  1. Windows
here is i have work out

Book1
ABCDEFGH
1DateBatchTrans RefChq/Db/Cr NameDescriptionQuantityThis PeriodCumulative
206-08-2020 0:00202008CBC2790806CV27003FINAL SETTLEMENT - DBB29396 - KAILASH VISHVAKARMA - CHQ. NO. 62025100174
318-08-2020 0:00202008CBC1256081CV27104PAID LEAVE SETTLEMENT-DBB29634-AMRO SAYED SOLIMAN ABOUELGHIT-CHQ-NO-62032900569
419-08-2020 0:00202008CBC2790819CV27119FINAL SETTLEMENT - DBB16673 - JAGDISH YADAV - CHQ. NO. 62034300379
519-08-2020 0:00202008CBC1687081CV27123FINAL SETTLEMENT-DBB13433-NALLELA MURALI-CHQ. NO.62034600926
6Ishaq026442644
727-08-2020 0:00202008CBC1687082CV27190PAID LEAVE SETTLEMENT-DBB30773-ISHAQ-CHQ. NO. 620395002644
8MINAT GUL017121712
918-08-2020 0:00202008CBC1256081CV27103PAID LEAVE SETTLEMENT-DBB33682-MINAT GUL-CHQ-NO-620328001712
10Sikander Hayat0536536
1118-08-2020 0:00202008CBC1687081CV27106FINAL SETTLEMENT-DBB28132-SIKANDER HAYAT-CHQ. NO. 62032500536
1220-08-2020 0:00202008CBC1256082CV27126FINAL SETTLEMENT-DBB35206-RANKALU LACHHAMAN REDDY-CHQ-NO-62035700349
1320-08-2020 0:00202008CBC1256082CV27127FINAL SETTLEMENT-DBB35205-RANKALU BIJAYA REDDY-CHQ-NO-62035600563
1429-08-2020 0:00202008CBC1687082CV27185FINAL SETTLEMENT-DBB31606-FAISAL SHAHZAD-CHQ. NO.62039300930
15AMJAD ABBASI0475475
Sheet1
 

mmr1

Board Regular
Joined
Aug 25, 2020
Messages
51
Office Version
  1. 365
Platform
  1. Windows
Thanks for your kind consideration, i have done it by looking your above posted sample
 

Watch MrExcel Video

Forum statistics

Threads
1,114,645
Messages
5,549,156
Members
410,902
Latest member
G Slim
Top