Combine column

[Zuy_NA]

Dear @HotChilli ,

Please help me to transfer data like this:

Class	Name1	Name2	Name3	Name4	Name5	Score1	Score2	Score3	Score4	Score5
1	A					6
2	B					7
3	C	A				8	9
4	D	A	B	C	A	5	3	4	3	7

Into:

Class	Name	Score
1	A	6
2	B	7
3	C	8
3	A	9
4	D	5
4	A	3
4	B	4
4	C	3
4	A	7

Thanks,

Best regards,

[HotChilli · Accepted solution]

Hello @Zuy_NA 

In Power Query, select the Class column and 'Unpivot other columns'.

Filter out the blank values in the Values column (use the dropdown).

Use 'split by delimiter' on the attribute column (Using 'By Non-digit to digit').

Select the 'Attribute1' column and choose Pivot from the Transform menu. Choose Value for the Values column and 'Don't aggregate' from the Advanced section.