Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm struggling to do something that I feel should be fairly simple;
I have two tables Table A which is like this:
Ref 1 | Ref 2 |
A | 1 |
B | 2 |
and Table B which is like this
1 | 2 | |
A | Value1 | Value2 |
B | Value3 | Value4 |
I want to add a column to Table A which uses the references to lookup what the value should be, so my end result in the case above would be
Ref 1 | Ref 2 | Lookup |
A | 1 | Value1 |
B | 2 | Value4 |
But I can't quite seem to get what I want in a neat way. Any help would be really appriciated as it'sd driving me slightly crazy.
Solved! Go to Solution.
Hi @EvelynN ,
You can acheive the above by the below solution,
1. You have to unpivot the second table. But i duplicated the table and created a table3/
2. Create a concart column in Table 1 with concat of (Ref1,Ref2).
3. Create a concat column in Table 3 with concat of (Ref1, Attribute).
4. Use Lookup function and lookup the data from table 3 to table1 based on the concat column.
Attaching the pbix link for your reference.
Here is the output
Regards,
Nikhil Chenna
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
This is not as simple as it seems.
Table B is matrix table, you would need to unpivot it first. Then you can link with Table A with the unpivoted table.
check this,
https://learn.microsoft.com/en-us/power-query/unpivot-column
Hi @EvelynN ,
You can acheive the above by the below solution,
1. You have to unpivot the second table. But i duplicated the table and created a table3/
2. Create a concart column in Table 1 with concat of (Ref1,Ref2).
3. Create a concat column in Table 3 with concat of (Ref1, Attribute).
4. Use Lookup function and lookup the data from table 3 to table1 based on the concat column.
Attaching the pbix link for your reference.
Here is the output
Regards,
Nikhil Chenna
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!