Forum Discussion
column to row relationship
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
Unpivot Table 1 in query editor then relate 'Table 1'[New Value Column] to 'Table 2'[Values].
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
5 Replies
v-qiuyu-msftCommunity Support
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 YuHello! 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.
mattbriceSolution Sage
Unpivot Table 1 in query editor then relate 'Table 1'[New Value Column] to 'Table 2'[Values].
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
