Forum Discussion
Unpivot columns
Hi,
The solution is probably easy, but I can't get it to work as the outcome is not the same if I manually calculate.
I have an excelsheet covering the following:
| Article nr | Article group | 0-30 number | 0- 30 value | 31-60 number | 31-60 value | 61-90 number | 61-90 value |
| 001 | Article name | 1 | € 10,50 | 5 | € 52,50 | 2 | € 21 |
I would like it to look like:
| Article nr | Article group | Age | Number | Value |
| 001 | Article name | 0-30 | 1 | € 10,50 |
| 001 | Article name | 31-60 | 5 | € 52,50 |
| 001 | Article name | 61-90 | 2 | € 21 |
Could someone help me with this please? When I tried unpivotting I got duplicates and when I tried to remove those I apparantly removed too much as the total sum did not match the excelsheet anymore.
Thanks in advance.
Hi lekkerbek ,
Please follow the below steps to acheive your request.
- First Unpivot the columns Fig 1.1
- Split the columns based on space delimiter to the Right-most delimiter Fig 1.2 and 1.3.
- Pivot the Number and Values to columns based on Value column Fig1.4.
Fig 1.1:Fig 1.2:
Fig 1.3:
Fig 1.4:
Best Regards,
Mail2inba4
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Hi lekkerbek ,
Please follow the below steps to acheive your request.
- First Unpivot the columns Fig 1.1
- Split the columns based on space delimiter to the Right-most delimiter Fig 1.2 and 1.3.
- Pivot the Number and Values to columns based on Value column Fig1.4.
Fig 1.1:Fig 1.2:
Fig 1.3:
Fig 1.4:
Best Regards,
Mail2inba4
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.The number column should reflect the number of articles in stock. The value column reflects the total value of the stock of that article. Those two can't be combined into one column.
