Forum Discussion
Combine column
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,
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.
1 Reply
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.