Forum Discussion
How Convert an excel data in transpose
Hi Experts,
I have a issue with a excel file with the following format:
You can see that there are a column by quarter/Product code
| Code | Description | Mar-23 | Jun-23 | Sep-23 | Dec-23 | Mar-24 | Jun-24 | Sep-24 | Dec-24 | Mar-25 | Jun-25 | Sep-25 |
| A01A002 | Red Car | 10.00 | 12.00 | 14.00 | 16.00 | 18.00 | 20.00 | 22.00 | 24.00 | 26.00 | 28.00 | 30.00 |
| A01A006 | Blue Car | 20.00 | 22.50 | 25.00 | 27.50 | 30.00 | 32.50 | 35.00 | 37.50 | 40.00 | 42.50 | 45.00 |
| A01A005 | Green Car | 23.00 | 24.00 | 25.00 | 26.00 | 27.00 | 28.00 | 29.00 | 30.00 | 31.00 | 32.00 | 33.00 |
| A01A026 | Black Car | 45.00 | 47.60 | 50.20 | 52.80 | 55.40 | 58.00 | 60.60 | 63.20 | 65.80 | 68.40 | 71.00 |
| A01A007 | Yellow Car | 32.00 | 30.00 | 28.00 | 26.00 | 24.00 | 22.00 | 20.00 | 18.00 | 16.00 | 14.00 | 12.00 |
| A01A027 | Pink Car | 12.00 | 7.00 | 2.00 | (3.00) | (8.00) | (13.00) | (18.00) | (23.00) | (28.00) | (33.00) | (38.00) |
and i need transform the format in something like this, where there are several rows by the same product code, 1 row by date/Amount
| Code | Description | Date | Amount |
| A01A002 | Red Car | Mar-23 | 10.00 |
| A01A002 | Red Car | Jun-23 | 12.00 |
| A01A002 | Red Car | Sep-23 | 14.00 |
| A01A002 | Red Car | Dec-23 | 16.00 |
| A01A002 | Red Car | Mar-24 | 18.00 |
| A01A002 | Red Car | Jun-24 | 20.00 |
| A01A002 | Red Car | Sep-24 | 22.00 |
| A01A002 | Red Car | Dec-24 | 24.00 |
| A01A002 | Red Car | Mar-25 | 26.00 |
| A01A002 | Red Car | Jun-25 | 28.00 |
| A01A002 | Red Car | Sep-25 | 30.00 |
| A01A006 | Blue Car | Mar-23 | 20.00 |
| A01A006 | Blue Car | Jun-23 | 22.50 |
| A01A006 | Blue Car | Sep-23 | 25.00 |
| A01A006 | Blue Car | Dec-23 | 27.50 |
| A01A006 | Blue Car | Mar-24 | 30.00 |
| A01A006 | Blue Car | Jun-24 | 32.50 |
| A01A006 | Blue Car | Sep-24 | 35.00 |
| A01A006 | Blue Car | Dec-24 | 37.50 |
| A01A006 | Blue Car | Mar-25 | 40.00 |
| A01A006 | Blue Car | Jun-25 | 42.50 |
| A01A006 | Blue Car | Sep-25 | 45.00 |
Is it Possible?
I really appreciate your help
Regards
Hello gomezc73
You can use unpivot in power query to achieve this. Select all the columns expect code, description and do unpivot selected columns as below.Let me know if this helps!
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
2 Replies
Hello gomezc73
You can use unpivot in power query to achieve this. Select all the columns expect code, description and do unpivot selected columns as below.Let me know if this helps!
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!Worked perfect. Thank you!!!
