Forum Discussion
Lookup, new table?
Hi, I pull from SQL server and have a handful of tables. One table, dbo.contacts and dbo.audits, are the primary tables for this question.
For my audit table, I have 4 contacts, contact1, contact2, contact3 and contact4 and need to display all four in my table. The audit table works fine overall. For my table within Power BI, how do I lookup the contacts based on their contact_ID from the contacts table?
dbo.audit example
| Audit (audit_ID) | Contact1 | Contact2 | Contact3 | Contact4 |
| 1 | 1 | 3 | 2 | 4 |
| 2 | 5 | 3 | 2 | 4 |
dbo.contact example
| Contact (contact_ID) | Name (full_name) |
| 1 | John Smith |
| 2 | Joe Smith |
| 3 | Tom Smith |
| 4 | Jane Smith |
Hi ez1138 ,
You can unpivot columns for dbo.audit and you can get a new table structure:
And you can use merge queries or lookupvalue to get contacts:
For merge queries:
And for lookupvalue function:
Name = LOOKUPVALUE('dbo contact'[Name (full_name)],'dbo contact'[Contact (contact_ID)],'dbo.audit'[Contact ID])If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
3 Replies
ez1138 , Can you unpivot the first column and then remove contract from the column and get id
Hi, apologize as I went through the link and tried it and maybe made a mistake or two.
Is there a LOOKUPVALUE command I can use to populate a new column?
Hi ez1138 ,
You can unpivot columns for dbo.audit and you can get a new table structure:
And you can use merge queries or lookupvalue to get contacts:
For merge queries:
And for lookupvalue function:
Name = LOOKUPVALUE('dbo contact'[Name (full_name)],'dbo contact'[Contact (contact_ID)],'dbo.audit'[Contact ID])If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
