Transforming uneven data

[PhillipB]

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.

[BiNavPete]

Hi @PhillipB 

Not sure how to manage more than 5 products but check out power query in attached PBIX.

Link Here.