PQ Help with Arranging Tables for Relationships

[redholt]

I think this is a relatively straightforward one but I cannot for the life of me figure it out. I have some survey data that has respondents in rows and questions in columns with the responses to each question being a number which in turn relates to a text answer in another table. 

Table 1 contains the respondents and question data (numbers) e.g.

Person	Male or Female?	Favourite Colour
1	2	3
2	1	2

and Table 2 is a table listing each question, number and the correspoding text e.g.

Male or Female?	1	Male
Male or Female?	2	Female
Favourite Colour	1	Red
Favourite Colour	2	Green
Favourite Colour	3	Blue
Favourite Colour	4	Orange

What I want to do is have Table 1 populated with the corresponding text answer in table 2 (so Person 1 would be Female, Blue) but I cant for the life of me figure it out. Could anyone nudge me in the right direction? 

Many Thanks

[redholt]

Thanks @m_dekorte that was exactly it. Still not got my head around unpivoting but it seems really powerful! 

[m_dekorte · Accepted solution]

Hi @redholt,

Select the Person column in Table 1, on the Transform tab select: Unpivot Columns, Unpivot Other Columns

Now you can Merge Table 1 and Table 2 setting the appropriate fields as keys (hold down CTRL when you select more than one combination) like so, the [Attribute] from Table 1 with Column 1 from Table 2 AND the [Value] from Table 1 with Column 2 from Table 2

Ps. If this helps solve your query please mark this post as Solution, thanks!