Multiple IDs in a relationship

[Anonymous]

Hello,

I have a lot of data in Excel (as source files) and need to create some relationships between the two, however it's not that straight forward;

• Table 1
  • List of roles
  • 1 role to many names and skills. (currently stored as text)
• Table 2
  • List of names and skills
  • 1 ID for each

Currently in Table 1, I have one line for each role, and a notes column to say that numbers 1,2,3,4 from table 2 match this row. Table 2 is just a list of names and skills with an ID. 

I want to build a relationship between the two so I can do some pivots, however is there a way to store the multiple IDs used in table 1 in a single cell like an array like {1,2,3,4} and the relationship will use that. 

Essentially I want to create a pivot table showing Role, All suitable candidates. Where two roles may have the same candidate. 

Any ideas? 

[v-henryk-mstf]

Hi @Anonymous ,

Whether the advice given by @danextian  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[danextian · Accepted solution]

Hi @Anonymous ,

A relationship in Power BI is between the as-is values in a column in one table and those in another column in another table. If you want to have a relationship between {1,2,3,4} in another table, each of these values must be in a row.

It would be easier for us to visualize what you're trying to achieve if you posted a sample data and then explain further from there.

[Anonymous]

I think I have a solution but not well suited to excel. The best solution I can think of is to have a list of roles (table 1) with IDs, List of candidates (table 2) with IDs, then a third lookup table with Role ID : Candidate ID, one to many each side. The only issue in Excel is maintaining this lookup table with many people working in the sheet which I dont think would be possible. I could do a Power automate of when the sheet is changed scan for changes and update the lookup, or better yet build a power app rather than use excel, but think its a bit overkill for my situation. I think I'll just build and manually maintain the lookup

[tamerj1]

Hi @Anonymous 
In order to be able to build a relationship you need to new create a calculated table out of table1 increasing the number of rows by duplicating them in order to have one row for each name and skill. Most probably this would generate a huge table with a very large number of rows most of them are redundant. 
Yet you can still build your pivot table following other methods but you need to share some sample data along with the expected results in order to be able to support you further.  Thank you.