Transpose data?

[Gids007]

Hi There,

I need a way of transposing this data...12 columns, there will be about 200 rows of data...

First Name	Last Name	Region	Manager	Patch	Q1	Q2	Q3	Q4	Q5	Q6	Q7
gideon	clewlow	NI	Todd Stocker	Rotorua	1	1	1	0	1	0	0
jane	brice	SI	Alex Moore	Timaru	1	1	0	0	1	0	0

<colgroup><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>

to...

First Name	Last Name	Region	Manager	Patch	Question	Score
gideon	clewlow	NI	Todd Stocker	Rotorua	Q1	1
gideon	clewlow	NI	Todd Stocker	Rotorua	Q2	1
gideon	clewlow	NI	Todd Stocker	Rotorua	Q3	1
gideon	clewlow	NI	Todd Stocker	Rotorua	Q4	0
gideon	clewlow	NI	Todd Stocker	Rotorua	Q5	1
gideon	clewlow	NI	Todd Stocker	Rotorua	Q6	0
gideon	clewlow	NI	Todd Stocker	Rotorua	Q7	0
jane	brice	SI	Alex Moore	Timaru	Q1	1
jane	brice	SI	Alex Moore	Timaru	Q2	1
jane	brice	SI	Alex Moore	Timaru	Q3	0
jane	brice	SI	Alex Moore	Timaru	Q4	0
jane	brice	SI	Alex Moore	Timaru	Q5	1
jane	brice	SI	Alex Moore	Timaru	Q6	0
jane	brice	SI	Alex Moore	Timaru	Q7	0

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Thank you

 

[sandy666]

with PowerQuery (Get&Transform) UnPivot selected columns (in this case Q columns)
(or UnPivot Other Columns - select all except Q columns)

First Name	Last Name	Region	Manager	Patch	Q1	Q2	Q3	Q4	Q5	Q6	Q7
gideon	clewlow	NI	Todd Stocker	Rotorua	1​	1​	1​	0​	1​	0​	0​
jane	brice	SI	Alex Moore	Timaru	1​	1​	0​	0​	1​	0​	0​
First Name	Last Name	Region	Manager	Patch	Question	Score
gideon	clewlow	NI	Todd Stocker	Rotorua	Q1	1​
gideon	clewlow	NI	Todd Stocker	Rotorua	Q2	1​
gideon	clewlow	NI	Todd Stocker	Rotorua	Q3	1​
gideon	clewlow	NI	Todd Stocker	Rotorua	Q4	0​
gideon	clewlow	NI	Todd Stocker	Rotorua	Q5	1​
gideon	clewlow	NI	Todd Stocker	Rotorua	Q6	0​
gideon	clewlow	NI	Todd Stocker	Rotorua	Q7	0​
jane	brice	SI	Alex Moore	Timaru	Q1	1​
jane	brice	SI	Alex Moore	Timaru	Q2	1​
jane	brice	SI	Alex Moore	Timaru	Q3	0​
jane	brice	SI	Alex Moore	Timaru	Q4	0​
jane	brice	SI	Alex Moore	Timaru	Q5	1​
jane	brice	SI	Alex Moore	Timaru	Q6	0​
jane	brice	SI	Alex Moore	Timaru	Q7	0​

[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Only Selected Columns" = Table.Unpivot(Source, {"Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7"}, "Question", "Score")
in
    #"Unpivoted Only Selected Columns"[/SIZE]

 

[majidsiddique]

copy the data and goto paste special and see the last one select transpose and OK.