Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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
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.
Thanks All, UnPivot other columns worked well for me!
Unpivot Table 1 in query editor then relate 'Table 1'[New Value Column] to 'Table 2'[Values].
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |