Forum Discussion
PQ Help with Arranging Tables for Relationships
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
Hi Anonymous,
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!
2 Replies
- m_dekorte
Resident Rockstar
Hi Anonymous,
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!
Thanks m_dekorte that was exactly it. Still not got my head around unpivoting but it seems really powerful!