Aug 15 2022 04:09 AM
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;
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.
Aug 15 2022 07:34 AM
Solution@chrisingham Does this get you anywhere? I used PowerQuery to generate the green table.
Aug 15 2022 07:38 AM
@Jan Karel PieterseYes, that perfectly fixes my problem! Thank you for this, saved me a lot of effort :)
Aug 15 2022 07:34 AM
Solution@chrisingham Does this get you anywhere? I used PowerQuery to generate the green table.