Create relationship between a column values and many other columns that have those values as names

[javiercmh]

Hi, 

I have the data from a survey which contains the items as columns like so (sorry this forum deleted my html tables 😞 😞

id, it1, it2, it3

online1, 4, 2, 5

online2, 1, 3, 4

Then I have a relationships table that looks like this:

it, Dimension

t1, Openness to experience

t2, Openness to experience

t3, Extraversion

Now, what I'd like to do is to somehow relate table 1 with table 2, so that when I visualize the survey results, I could easily filter by dimension.

Thanks in advance!

Javier 

[MFelix · Accepted solution]

Hi @javiercmh,

The best way is on the query editor unpivot the "it1, it2,..." columns that way you would get a table with 3 values:

IDAttributeValue

Online 1	it1	1
Online 1	it2	4
Online 1	it3	2
Online 2	it1	2
Online 2	it2	1
Online 2	it3	3

then you could make the link between the two tables and work your information as needed.

Regards,

MFelix

[v-yuezhe-msft]

@MFelix,

Adding to other's post, you need to replace it1,it2,it3 with t1, t2, t3 in the unpivotted table in order to match the rows in the two tables.



Regards,
Lydia