Transpose or UnPivot or Something else

[cmcreynolds]

Hello -

I am needing to do something and I'm not sure what the verbage is used to describe it.

I want to take this table (the way my data is currently set up):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Reviewer
[/TD]
[TD]Citation
[/TD]
[TD]Ques1
[/TD]
[TD]Ques2
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]B
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]C
[/TD]
[TD]2
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]B
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]C
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]



And turn it into this table (how I need it to run Cohen's Kappa):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Citation
[/TD]
[TD]R1Ques1
[/TD]
[TD]R2Ques1
[/TD]
[TD]R1Ques2
[/TD]
[TD]R2Ques2
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And I'm not sure how the "new" field names would be even created/populated? I just listed what I thought the result would be.

I put this thread in Power BI because I know PowerQuery has an "unpivot" option, but I'm not sure how it would help?

Thank you for direction.

 

[Matt Allington]

Assuming your answers are numbers, you can do this in Power Query. Here is a workbook showing the steps

https://www.dropbox.com/s/yprxv2qig9azwr2/unpivot pivot.xlsx?dl=0

 

[cmcreynolds]

How different would it be if they were text?

 

[Matt Allington]

When you pivot data (turn a single column of multiple values into multiple columns), you have to specify how to aggregate the data. The standard way a pivot table does this is to SUM the data. Of course this only is necessary when there are 2 possible values for a cell. So you can sum them, select the MIN, the MAX, the Average, or what ever. The point is that when there is more than 2 value to be placed in a cell, you have to decide "how to do it". I just tested this again and set the Values to TEXT. As long as there is only ever 1 value for each cell, you can select "don't aggregate" and all will be sweet. If there is more than 1 value and you select "don't aggregate", then you will get errors.

Hope that makes sense