I have data that looks as in below table
| Name | Subject 1 | Mark 1 | Subject 2 | Mark 2 |
|---|---|---|---|---|
| Test | Biology | 90 | Physics | 80 |
and would like to convert the above data as below.
| Name | Subject | Mark |
|---|---|---|
| Test | Biology | 90 |
| Test | Physics | 80 |
CodePudding user response：
I would advise you to create an extra column, where you copy the "Name" field:
| Name | Subject 1 | Mark 1 | Name | Subject 2 | Mark 2 |
|---|---|---|---|---|---|
| Test | Biology | 90 | =A2 | Physics | 80 |
... and then copy the right half under the existing table (paste as values).
CodePudding user response：
What you're looking for is called "Unpivot columns" in Excel Power query.
You need to load this table in Power query and select all the "SubjectN" columns and click Unpivot. Refer to the following video for example