column to row relationship

[Anonymous]

Hi,

I have two tables(table1 and table 2), the column names of table 1(1,2,3,4,5)

is refering to values of table 2, how can we get a column(table1) to row(table2) relationship.

Table 1

1	2	3	4	5
h	o	f	g	a
h	k	g	s	e
i	j	f	j	s
o	j	j	f	f

Table 2

Values	ColumnHeading
1	What is your name?
2	Where are you from?
3	How are you doing?
4	Can I help you?
5	Where are you going to?

Thanks,

Ravi

[mattbrice · Accepted solution]

Unpivot Table 1 in query editor then relate 'Table 1'[New Value Column] to 'Table 2'[Values].

[Gatot]

Hi,

I have two tables , table 1 is main table and table 2 is Unpivot column. I m trying to make relationship between them but unfortunately an error message shown ("Column "INTNR" in Tables 2 contains a duplicate value and not allowed).

Tables 1

INTNR	D2	D3	D4	D4_1	D6
70910	First vehicle ever	Male		35 ~ 39 years	Self employee
70911	Additional vehicle for my household	Male		Older than 50, specify	Self employee
70912	Additional vehicle for my household	Female		40 ~ 49 years	Housewife
70913	First vehicle ever	Male		40 ~ 49 years	Government employee
70914	Replacement vehicle for my household	Male		40 ~ 49 years	Private company employee
70915	Replacement vehicle for my household	Female		35 ~ 39 years	Private company employee

Table 2 (unpivot column)

INTNR	Attribute	Value
70910	NATIONAL	NATIONAL
70910	REGION	REGION
70910	DEALER	Blank
70911	NATIONAL	NATIONAL
70911	REGION	REGION
70911	DEALER	Blank
70912	NATIONAL	NATIONAL
70912	REGION	REGION
70912	DEALER	Blank
70913	NATIONAL	NATIONAL
70913	REGION	REGION
70913	DEALER	Blank
70914	NATIONAL	NATIONAL
70914	REGION	REGION
70914	DEALER	Blank
70915	NATIONAL	NATIONAL
70915	REGION	REGION
70915	DEALER	Blank

Thanks,

Gatot

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

To relate two tales, there should have a common column in those two tables. In your scenario, you can open Query Editor, select the query Table1, select all columns then click Unpivot Columns.

Then apply the changes, go to the report, create a relationship. For more information, please check attached .pbix file.

Best Regards,
Qiuyun Yu

[cynthiaAA]

Hello! Thank you for sharing this, it was very helpful and worked! But in the process I lost the data from the original Table1. Is there a way to unpivot, create the relationship, and then transform back to the original table while maintaining the new relationship? I tried pivoting, but it didn't work.

[Anonymous]

Thanks All, UnPivot other columns worked well for me!

[mattbrice · Accepted solution]

Unpivot Table 1 in query editor then relate 'Table 1'[New Value Column] to 'Table 2'[Values].