Lookup, new table?

[ez1138]

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

[v-deddai1-msft · Accepted solution]

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

[amitchandak]

@ez1138 , Can you unpivot the first column and then remove contract from the column and get id

https://radacad.com/pivot-and-unpivot-with-power-bi

[ez1138]

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?