Baconzilla
New Member
- Joined
- Aug 23, 2018
- Messages
- 2
I tried searching for the answer, but the problem is that I don't even know how to phrase the question for a search. I receive a csv file with data broken out into different groups of rows and columns. I want to clean up all of the data into one table. I tried highlighting the examples below to make it easier to follow:
I start with a csv file that contains this:
Excel 2010
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
I want the end result to look like this:
Excel 2010
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
The actual csv file has hundreds of these datasets. Can Power Query do this? If so, what tools/features should I read up on in order to do this?
Thank you!
I start with a csv file that contains this:
Excel 2010
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Company | 901 | |||||||||||||||
2 | Scenario | ||||||||||||||||
3 | Year | 2018 | |||||||||||||||
4 | Stat Code | ||||||||||||||||
5 | |||||||||||||||||
6 | Account | Acct Descr | Dept | Prod | Proj | January | February | March | April | May | June | July | August | September | October | November | December |
7 | 10001 | 25 | 2,905 | 3,149 | 2,600 | 2,666 | 3,553 | 2,822 | 3,907 | 4,018 | 3,532 | 2,879 | 3,337 | 3,716 | |||
8 | 10002 | 25 | 59 | 26 | 1,524 | 779 | 1,371 | 2,432 | 3,200 | 2,683 | 1,958 | 2,759 | 2,449 | 1,934 | |||
9 | 10003 | 25 | 3 | 3 | 4 | 4 | 4 | 4 | 5 | 4 | 5 | 4 | 5 | 4 | |||
10 | 10004 | 25 | 20 | 11 | 289 | 202 | 367 | 693 | 690 | 547 | 407 | 520 | 615 | 647 | |||
11 | |||||||||||||||||
12 | Company | 902 | |||||||||||||||
13 | Scenario | ||||||||||||||||
14 | Year | 2018 | |||||||||||||||
15 | Stat Code | ||||||||||||||||
16 | |||||||||||||||||
17 | Account | Acct Descr | Dept | Prod | Proj | January | February | March | April | May | June | July | August | September | October | November | December |
18 | 10001 | 25 | 3,782 | 3,710 | 3,954 | 3,794 | 3,952 | 3,364 | 3,812 | 3,103 | 3,927 | 4,092 | 2,871 | 3,205 | |||
19 | 10002 | 25 | 578 | 723 | 2,213 | 1,500 | 1,431 | 2,940 | 3,390 | 2,914 | 1,849 | 2,448 | 2,534 | 2,396 | |||
20 | 10003 | 25 | 4 | 4 | 5 | 6 | 4 | 6 | 6 | 4 | 6 | 5 | 4 | 3 | |||
21 | 10004 | 25 | 122 | 232 | 415 | 212 | 355 | 707 | 678 | 726 | 611 | 544 | 606 | 605 | |||
22 | |||||||||||||||||
23 | Company | 903 | |||||||||||||||
24 | Scenario | ||||||||||||||||
25 | Year | 2018 | |||||||||||||||
26 | Stat Code | ||||||||||||||||
27 | |||||||||||||||||
28 | Account | Acct Descr | Dept | Prod | Proj | January | February | March | April | May | June | July | August | September | October | November | December |
29 | 10001 | 25 | 9,812 | 8,039 | 10,244 | 9,737 | 8,458 | 8,816 | 9,476 | 11,160 | 8,820 | 10,044 | 10,620 | 10,974 | |||
30 | 10002 | 25 | 873 | 1,080 | 3,608 | 4,338 | 5,397 | 9,479 | 11,195 | 7,887 | 6,143 | 7,210 | 6,639 | 5,403 | |||
31 | 10003 | 25 | 6 | 6 | 5 | 5 | 6 | 6 | 5 | 4 | 4 | 5 | 4 | 4 | |||
32 | 10004 | 25 | 189 | 204 | 897 | 682 | 1,113 | 1,856 | 1,657 | 1,448 | 1,577 | 2,001 | 1,752 | 1,472 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sample Data
I want the end result to look like this:
Excel 2010
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Company | Year | Account | Dept | January | February | March | April | May | June | July | August | September | October | November | December |
2 | 901 | 2018 | 10001 | 25 | 2,905 | 3,149 | 2,600 | 2,666 | 3,553 | 2,822 | 3,907 | 4,018 | 3,532 | 2,879 | 3,337 | 3,716 |
3 | 901 | 2018 | 10002 | 25 | 59 | 26 | 1,524 | 779 | 1,371 | 2,432 | 3,200 | 2,683 | 1,958 | 2,759 | 2,449 | 1,934 |
4 | 901 | 2018 | 10003 | 25 | 3 | 3 | 4 | 4 | 4 | 4 | 5 | 4 | 5 | 4 | 5 | 4 |
5 | 901 | 2018 | 10004 | 25 | 20 | 11 | 289 | 202 | 367 | 693 | 690 | 547 | 407 | 520 | 615 | 647 |
6 | 902 | 2018 | 10001 | 25 | 3,782 | 3,710 | 3,954 | 3,794 | 3,952 | 3,364 | 3,812 | 3,103 | 3,927 | 4,092 | 2,871 | 3,205 |
7 | 902 | 2018 | 10002 | 25 | 578 | 723 | 2,213 | 1,500 | 1,431 | 2,940 | 3,390 | 2,914 | 1,849 | 2,448 | 2,534 | 2,396 |
8 | 902 | 2018 | 10003 | 25 | 4 | 4 | 5 | 6 | 4 | 6 | 6 | 4 | 6 | 5 | 4 | 3 |
9 | 902 | 2018 | 10004 | 25 | 122 | 232 | 415 | 212 | 355 | 707 | 678 | 726 | 611 | 544 | 606 | 605 |
10 | 903 | 2018 | 10001 | 25 | 9,812 | 8,039 | 10,244 | 9,737 | 8,458 | 8,816 | 9,476 | 11,160 | 8,820 | 10,044 | 10,620 | 10,974 |
11 | 903 | 2018 | 10002 | 25 | 873 | 1,080 | 3,608 | 4,338 | 5,397 | 9,479 | 11,195 | 7,887 | 6,143 | 7,210 | 6,639 | 5,403 |
12 | 903 | 2018 | 10003 | 25 | 6 | 6 | 5 | 5 | 6 | 6 | 5 | 4 | 4 | 5 | 4 | 4 |
13 | 903 | 2018 | 10004 | 25 | 189 | 204 | 897 | 682 | 1,113 | 1,856 | 1,657 | 1,448 | 1,577 | 2,001 | 1,752 | 1,472 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Intended Result
The actual csv file has hundreds of these datasets. Can Power Query do this? If so, what tools/features should I read up on in order to do this?
Thank you!