Multiple IDs in a relationship

[chrisingham]

Hello,

I have a lot of data in Excel 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? Cant really use power BI due to others working in the data. 

[#2 · Accepted solution]

@chrisingham Does this get you anywhere? I used PowerQuery to generate the green table.

[#3]

@Jan Karel PieterseYes, that perfectly fixes my problem! Thank you for this, saved me a lot of effort :)