cancel
Showing results for
Did you mean:
Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Solution Sage

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!

2 REPLIES 2
Frequent Visitor

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

Solution Sage

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!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors