I have a matrix like this but far more columns and rows. I want to use power query to transpose columns to rows and rows to columns. I want to use power query and it seems it should work, however, I am not able to promote first row to headers . For some reason, only column 1 has the header name ( see at the bottom).
I would greatly appreciate your help.
ORIGINAL MATRIX
TRANSPOSED MATRIX - column2 etc should contain dates
Thank you!
I would greatly appreciate your help.
ORIGINAL MATRIX
| BSDate | Alumina | Aluminum | AUD | BRL | CAD | CHF | CNY | Coal |
| 12/31/2011 | 309.44 | 1,970.50 | 1.0155 | 0.5368 | 0.9801 | 1.0626 | 0.1589 | 106.6000 |
| 1/31/2012 | 317.11 | 2,257.50 | 1.0668 | 0.5714 | 1.0011 | 1.0942 | 0.1585 | 105.2500 |
| 2/29/2012 | 318.58 | 2,301.00 | 1.0806 | 0.5828 | 1.0070 | 1.1164 | 0.1589 | 103.5000 |
| 3/31/2012 | 316.20 | 2,098.50 | 1.0403 | 0.5485 | 1.0027 | 1.1083 | 0.1587 | 103.5000 |
| 4/30/2012 | 319.50 | 2,058.00 | 1.0441 | 0.5280 | 1.0191 | 1.1017 | 0.1585 | 97.7500 |
| 5/31/2012 | 320.53 | 1,973.50 | 0.9728 | 0.4944 | 0.9730 | 1.0327 | 0.1569 | 88.6500 |
| 6/30/2012 | 308.21 | 1,834.50 | 1.0165 | 0.4958 | 0.9747 | 1.0464 | 0.1574 | 90.1000 |
| 7/31/2012 | 312.67 | 1,862.50 | 1.0506 | 0.4874 | 0.9987 | 1.0209 | 0.1570 | 87.3500 |
| 8/31/2012 | 319.13 | 1,849.00 | 1.0307 | 0.4931 | 1.0082 | 1.0420 | 0.1574 | 87.0000 |
| 9/30/2012 | 324.10 | 2,094.00 | 1.0454 | 0.4929 | 1.0215 | 1.0688 | 0.1591 | 85.8000 |
| 10/31/2012 | 322.17 | 1,882.50 | 1.0378 | 0.4923 | 1.0002 | 1.0738 | 0.1603 | 82.0000 |
| 11/30/2012 | 327.87 | 2,079.00 | 1.0422 | 0.4745 | 1.0070 | 1.0785 | 0.1606 | 90.9000 |
| 12/31/2012 | 331.16 | 2,040.00 | 1.0397 | 0.4894 | 1.0070 | 1.0930 | 0.1591 | 88.3000 |
| 1/31/2013 | 333.50 | 2,073.50 | 1.0434 | 0.5029 | 1.0026 | 1.0994 | 0.1592 | 86.7500 |
TRANSPOSED MATRIX - column2 etc should contain dates
| BSDate | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 |
| Aluminum | 2285 | 2496 | 2371 | 2512.5 | 2753.5 | 2712 | 2550.5 | 2501 |
| AUD | 0.7342 | 0.7572 | 0.743 | 0.7165 | 0.7593 | 0.7519 | 0.7423 | 0.7664 |
| BRL | 0.4277 | 0.452612 | 0.472144 | 0.460405 | 0.4735 | 0.43465 | 0.462107 | 0.459876 |
| CAD | 0.8597 | 0.874432 | 0.878812 | 0.856898 | 0.8903 | 0.906865 | 0.896861 | 0.884251 |
| CHF | 0.7604 | 0.782228 | 0.762718 | 0.767754 | 0.7938 | 0.822436 | 0.816526 | 0.812282 |
| CNY | 0.123913 | 0.124057 | 0.124355 | 0.12474 | 0.124743 | 0.124665 | 0.125089 | 0.125486 |
Thank you!