cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
javiercmh Frequent Visitor
Frequent Visitor

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

Hi, 

 

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

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Create relationship between a column values and many other columns that have those values as nam

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 1it11
Online 1it24
Online 1it32
Online 2it12
Online 2it21
Online 2it33

 

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

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: Create relationship between a column values and many other columns that have those values as nam

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 1it11
Online 1it24
Online 1it32
Online 2it12
Online 2it21
Online 2it33

 

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

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Moderator v-yuezhe-msft
Moderator

Re: Create relationship between a column values and many other columns that have those values as nam

@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.

1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 1,623 guests