How to join two tables without unique values?

[Sitak_Johar]

Hello All,

I am having trouble to create a relationship between 2 tables to create the expected output visual.

I have two tables: Table 1 and Table 2
Sample of Table 1 is:

Server Name	Contact	Contact.1	Contact.2	Contact.3	Contact.4	Contact.5
ABC	(delimiter)A(delimeter)B(delimiter)C	A	B	C
DEF	(delimiter)A(delimeter)D(delimiter)E(delimiter)F	A	D	E	F
GHI	(delimiter)A(delimeter)E(delimiter)G(delimiter)H(delimiter)I	A	E	G	H	I

Some background on Table 1:

• The original table only has the 1st 2 columns - server name and contact
• Contact column has all the contact ID's for a particular server but they are all contained in one text
•  Hence I've split up the Contact column using Power Pivot with a common delimiter and the results are the columns starting from Contact.1
• The number of contacts are not pre-defined, it could be anywhere between 1 or 50 or even more

Sample of Table 2:

Resource ID	Resource Name
A	Brian
B	David
C	Julien
D	Michael
E	Jack
F	Sam

Some background on Table 2:

• For each Resource ID, we have the resource Name

Relation between Table 1 and Table 2:

• The contact field's starting with contact.1 etc. are the resource ID in the 2nd table
• Here I am having difficulty to create the appropriate relationship in Power BI since the values in Table 1 are lined up in a row instead of a column

Expected Output:

Server Name	Resource ID	Resource Name
ABC	A	Brian
B	David
C	Julien
DEF	A	Brian
D	Michael
E	Jack
F	Sam

OR it could be the following output:

Resource ID	Resource Name	Server Name
A	Brian	ABC, DEF
B	David	ABC
C	Julien	ABC
D	Michael	DEF
E	Jack	DEF
F	Sam	DEF

Any one of the output could work.

Thanks,

VH

[parry2k · Accepted solution]

@Sitak_Johar You can split your values into rows and then from there it will be easier to set the relationship and contacts will be on seperate rows instead of multiple columns.