Forum Discussion
How to join two tables without unique values?
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
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.
1 Reply
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.