Forum Discussion
Referencing one Table with Another
Hi all!
My problem is as follows:
I have two tables:
Table1
| ID | Attribute 1 | Attribute 2 | Attribute 3 |
| 1 | x1 | r1 | z4 |
| 2 | x4 | r1 | z2 |
| 3 | x4 | r4 | z4 |
Table2
| Attributecode | Reference |
| x1 | Country code AR |
| x2 | Country code US |
| ... | |
| z4 | Speed range 500 |
I would like to have a result file or result table, that combines both and does not show the code but shows the Reference:
Tableresult
| ID | Attribute 1 | Attribute 2 | Attribute 3 |
| 1 | Country Code AR | xxxxx | Speed range 500 |
| 2 | Country Code ZA | xxxxx | Speed range 300 |
| 3 | Country Code ZA | rrrrrrr | Speed range 500 |
Is this possible to do without manually updating the table in Excel for example?
Best,
Nahuel!
- Load Table1 and Table2 into Power Query.
- Unpivot Table1:
Select Attribute 1, Attribute 2, Attribute 3 → Right-click → Unpivot Columns.
- Merge with Table2:
Merge on Value (from Table1) and Attributecode (from Table2) → Left Join.
- Expand the Reference column from Table2.
- Pivot back:
Select Attribute column → Pivot Column → Use Reference as values.
5 Replies
- Load Table1 and Table2 into Power Query.
- Unpivot Table1:
Select Attribute 1, Attribute 2, Attribute 3 → Right-click → Unpivot Columns.
- Merge with Table2:
Merge on Value (from Table1) and Attributecode (from Table2) → Left Join.
- Expand the Reference column from Table2.
- Pivot back:
Select Attribute column → Pivot Column → Use Reference as values.
Hi NahuelK ,
Yes, it's absolutely possible to do this without manually updating Excel. In Power BI or Power Query, you can transform the data dynamically. First, you'd unpivot Table1 so that the attributes (Attribute 1, 2, 3) become rows instead of columns, keeping the ID alongside each. This creates a long table where each row shows an ID, the attribute name, and its value. Then, you merge this unpivoted table with Table2 by matching the attribute values (like x1, r1, z4) to the Attributecode in Table2. This brings in the corresponding Reference description. Once that’s done, you pivot the table back using the attribute name so that each attribute returns to its original column layout, but now showing the descriptive Reference instead of the code. The result is a fully dynamic transformation where you no longer see cryptic codes but readable values like “Country Code AR” and “Speed range 500,” all without touching the data manually.
Best regards,
Hi NahuelK ,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.
Regards,Chaithanya
Hi @NahuelK ,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.
Regards,Chaithanya
Hi @NahuelK ,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.
Regards,Chaithanya
