Multiple IDs in a relationship

New Contributor



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 Replies
best response confirmed by chrisingham (New Contributor)

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

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