Forum Discussion
Transforming uneven data
Hi,
I need to be able to change the way this data is displayed.
| Order no | Part | Qty |
| 1001 | 123456-01 | 18000 |
| 1001 | SSL446 | 1 |
| 1001 | SSJ887 | 5 |
| 1002 | 123568-03 | 265 |
| 1002 | SSL445 | 2 |
| 1002 | SWD584 | 8 |
| 1002 | EED854 | 4 |
| 1002 | RTG568 | 3 |
| 1003 | 856714-03 | 2000 |
| 1003 | SEF561 | 5 |
| 1003 | SSL446 | 6 |
| 1004 | 243578-03 | 6000 |
| 1004 | EED854 | 8 |
| 1004 | SSL446 | 10 |
| 1004 | RFY895 | 12 |
| 1004 | PLE559 | 6 |
| 1004 | RTG568 | 9 |
| 1005 | 143578-03 | 500 |
| 1005 | SEF561 | 2 |
| 1006 | 123708-02 | 6000 |
| 1006 | SSJ887 | 50 |
| 1006 | PLE559 | 10 |
| 1006 | SWD584 | 12 |
The parts with the ######-## format are the main part and the others under are sub parts but the output should be
| Order No | Part | Qty | Sub1 | Qty | Sub2 | Qty | Sub3 | Qty | Sub4 | Qty | Sub5 | Qty |
| 1001 | 123456-01 | 18000 | SSL446 | 1 | SSJ887 | 5 | ||||||
| 1002 | 123568-03 | 265 | SSL445 | 2 | SWD584 | 8 | EED854 | 4 | RTG568 | 3 | ||
| 1003 | 856714-03 | 2000 | SEF561 | 5 | SSL446 | 6 | ||||||
| 1004 | 243578-03 | 6000 | EED854 | 8 | SSL446 | 10 | RFY895 | 12 | PLE559 | 6 | RTG568 | 9 |
| 1005 | 143578-03 | 500 | SEF561 | 2 | ||||||||
| 1006 | 123708-02 | 6000 | SSJ887 | 50 | PLE559 | 10 | SWD584 | 12 |
The number of sub parts is not consistent.
1 Reply
Hi PhillipB
Not sure how to manage more than 5 products but check out power query in attached PBIX.
