Spreadsheet: 861731d1709573728-data-transpose-from-columns-to-rows-power-query-data-transpose
Sheet: Request

 | Project code | Location | Date | ABC | CDR | BBA | NNT | ADC | XCY | XTY | BBE | AAT | NNY | NNY2
 | 73489 | London | 2024-04-01 00:00:00 | 40329 | 21398 | 38526 | 40697 | 66085 | 3477 | 43387 | 70782 | 79379 | 57601 | 20257
 | 64067 | Enfileld | 2024-05-01 00:00:00 | 51020 | 26423 | 20463 | 46350 | 31059 | 29887 | 51437 | 44654 | 20794 | 6907 | 8385
 | 61406 | Yor | 2024-06-01 00:00:00 | 22409 | 79902 | 41199 | 35692 | 3914 | 11087 | 26120 | 84923 | 51947 | 60951 | 30099
 | 77340 | Derby | 2024-07-01 00:00:00 | 59718 | 85160 | 22080 | 67942 | 37583 | 79195 | 22888 | 84225 | 8120 | 2707 | 39056
 | 53579 | Crawley | 2024-08-01 00:00:00 | 26798 | 83298 | 25285 | 82601 | 16642 | 72504 | 80379 | 78173 | 34368 | 74970 | 9119
 | 76387 | Bury | 2024-09-01 00:00:00 | 16611 | 81126 | 35250 | 65035 | 38179 | 31742 | 53307 | 30380 | 2767 | 11346 | 14923
 | 66727 | North Shield | 2024-10-01 00:00:00 | 3498 | 61321 | 62498 | 69613 | 17997 | 57894 | 35773 | 11632 | 81933 | 35415 | 68455
 | 55423 |  | 2024-11-01 00:00:00 | 12813 | 43646 | 37653 | 14027 | 26750 | 69457 | 8879 | 24215 | 41671 | 33337 | 10747
 | 65273 |  | 2024-12-01 00:00:00 | 51285 | 50824 | 52932 | 15503 | 57902 | 71925 | 38587 | 30744 | 61611 | 35560 | 40655
 | 53198 |  | 2025-01-01 00:00:00 | 3626 | 11122 | 36213 | 21725 | 80988 | 83196 | 59744 | 7629 | 41314 | 80534 | 7979
 | 79211 |  | 2025-02-01 00:00:00 | 83253 | 46044 | 77674 | 54773 | 71904 | 6562 | 45043 | 34332 | 63521 | 2564 | 49743
 | 77050 |  | 2025-03-01 00:00:00 | 32328 | 80846 | 61230 | 6536 | 77421 | 84454 | 36643 | 40249 | 54247 | 65610 | 85066
 | 78826 |  | 2025-04-01 00:00:00 | 49284 | 17676 | 17555 | 55342 | 61992 | 15400 | 27066 | 67291 | 74896 | 74347 | 60502
 | 56193 |  | 2025-05-01 00:00:00 | 18937 | 8191 | 5612 | 64293 | 79908 | 10559 | 25406 | 72431 | 13855 | 68712 | 79501
 | 74798 |  | 2025-06-01 00:00:00 | 13301 | 41466 | 67758 | 16806 | 22600 | 18216 | 74432 | 62652 | 43762 | 82423 | 48510
 | 70250 |  | 2025-07-01 00:00:00 | 66841 | 79410 | 61725 | 76607 | 70954 | 75737 | 79442 | 15089 | 57110 | 19933 | 31659
 | 59894 |  | 2025-08-01 00:00:00 | 7627 | 50976 | 48668 | 22889 | 46877 | 13148 | 17127 | 79245 | 14701 | 50522 | 63956
 | 74903 |  | 2025-09-01 00:00:00 | 40624 | 12743 | 17343 | 3546 | 75469 | 2484 | 55834 | 17378 | 77054 | 62374 | 53466
E.g 1 | Manual Copy and transpose |  |  |  |  |  |  |  | Pivot table Attempt to transpose
 | Project code | Location | Date | Programmes | Amount
 | 73489 | London | 2024-04-01 00:00:00 | ABC | 40329 |  |  |  | Project code | Location | ABC | BBA | CDR
 | 73489 | London | 2024-04-01 00:00:00 | CDR | 21398 |  |  |  | 53198 | Yor | 3626 | 36213 | 11122
 | 73489 | London | 2024-04-01 00:00:00 | BBA | 38526 |  |  |  | 53579 | Crawley | 26798 | 25285 | 83298
 | 73489 | London | 2024-04-01 00:00:00 | NNT | 40697 |  |  |  | 55423 | London | 12813 | 37653 | 43646
 | 73489 | London | 2024-04-01 00:00:00 | ADC | 66085 |  |  |  | 56193 | North Shield | 18937 | 5612 | 8191
 | 73489 | London | 2024-04-01 00:00:00 | XCY | 3477 |  |  |  | 59894 | Yor | 7627 | 48668 | 50976
 | 73489 | London | 2024-04-01 00:00:00 | XTY | 43387 |  |  |  | 61406 | Yor | 22409 | 41199 | 79902
 | 73489 | London | 2024-04-01 00:00:00 | BBE | 70782 |  |  |  | 64067 | Enfileld | 51020 | 20463 | 26423
 | 73489 | London | 2024-04-01 00:00:00 | AAT | 79379 |  |  |  | 65273 | Enfileld | 51285 | 52932 | 50824
 | 73489 | London | 2024-04-01 00:00:00 | NNY | 57601 |  |  |  | 66727 | North Shield | 3498 | 62498 | 61321
 | 73489 | London | 2024-04-01 00:00:00 | NNY | 20257 |  |  |  | 70250 | Enfileld | 66841 | 61725 | 79410
 |  |  |  |  |  |  |  |  | 73489 | London | 40329 | 38526 | 21398
 |  |  |  |  |  |  |  |  | 74798 | London | 13301 | 67758 | 41466
 | And May will follow directly after April |  |  |  |  |  |  |  | 74903 | Derby | 40624 | 17343 | 12743
 | 64067 | Enfileld | 2024-05-01 00:00:00 |  | 51020 |  |  |  | 76387 | Bury | 16611 | 35250 | 81126
 | 64067 | Enfileld | 2024-05-01 00:00:00 |  | 26423 |  |  |  | 77050 | Crawley | 32328 | 61230 | 80846
 | 64067 | Enfileld | 2024-05-01 00:00:00 |  | 20463 |  |  |  | 77340 | Derby | 59718 | 22080 | 85160
 | 64067 | Enfileld | 2024-05-01 00:00:00 |  | 46350 |  |  |  | 78826 | Bury | 49284 | 17555 | 17676
 | 64067 | Enfileld | 2024-05-01 00:00:00 |  | 31059 |  |  |  | 79211 | Derby | 83253 | 77674 | 46044
 | 64067 | Enfileld | 2024-05-01 00:00:00 |  | 29887 |  |  |  | (blank) | (blank) | 600302 | 729664 | 881572
 | 64067 | Enfileld | 2024-05-01 00:00:00 |  | 51437 |  |  |  | Grand Total
 | 64067 | Enfileld | 2024-05-01 00:00:00 |  | 44654
 | 64067 | Enfileld | 2024-05-01 00:00:00 |  | 20794
 | 64067 | Enfileld | 2024-05-01 00:00:00 |  | 6907
 | 64067 | Enfileld | 2024-05-01 00:00:00 |  | 8385
 |  | This is the final outlook I am expecting to achieve
 |  | Project code | Location | Programmes |  | Total Prog Amount | 2024-04-01 00:00:00 | 2024-05-01 00:00:00 | 2024-06-01 00:00:00 | 2024-07-01 00:00:00 | 2024-08-01 00:00:00 | 2024-09-01 00:00:00 | 2024-10-01 00:00:00 | Total
 |  | 73489 | London | ABC
 |  | 73489 | Enfileld | CDR
 |  | 73489 | London | BBA
 |  | 73489 |  | NNT
 |  | 73489 |  | ADC
 |  | 73489 |  | XCY
 |  | 73489 |  | XTY
 |  | 73489 |  | BBE
 |  | 73489 |  | AAT
 |  | 73489 |  | NNY
 |  | 64067
 |  | 64067
 |  | 64067
 |  | 64067
 |  | 64067
 |  | 64067
 |  | 64067
 |  | 64067
 |  | 64067
 | Request
 | I have handover a huge data file that is not in the correct  rows for the programmes activities, i.e ABC,CDR,BBA
 | As in E.g 1, I have copied and transpose the data manually wherein the programmes activities should be in rows against each location and  project code,
 | if one project code has morethan one location it should be in rows format under the same location
 | I have attempted to pivot the table but the programmes activities highlighted in L to N is still coming up in the columns format.
 | Could someone please assist to advise the best possible way I can swiftly transpose my date as I have a large file to transpose into the correct format and doing it line by line will be time consuming