How to work with data in 2 SharePoint lists

[JasonLC]

I have two SharePoint Lists:
- Skills

- Employee

The skills list has two columns:
- Title

- SkillID

The Employee list has several columns, but the most important are:

- EmployeeName

- SelectedSkills

I have a Power App which the employee selects a list of skills they can support and saves their selected skill IDs in a text cell. The SelectedSkills cell looks like this:
85|62|55|201|205|329|310|204|56

Here are things I would like to report on in Power BI Desktop:
- Select a skill and all of the Employees matching that skill are shown

- Select an employee and see all of the skills they support

- See the % of employees which match one or more skills

However, since the SelectedSkills cell is just a cell of text values, I don't know how to do this. I can't create a table relationship, because the SelectedSkills cell is a bunch of text and isn't unique values which can be matched to the SkillID in the Skills SharePoint List. 

I've thought about creating a new table in Power BI Desktop and then populating that new table based on data from both lists, but don't really know how to do that either where it would all be dynamic after pulling updated data from SharePoint each time. 

Does anyone have any thoughts how to easily solve this?

Thank you!

[Ritaf1983 · Accepted solution]

Hi @JasonLC 
You need to split the selected skills into rows.
Please refer to my solution in the linked post, it includes a step-by-step guide + link to the pbix with the example

https://community.fabric.microsoft.com/t5/Desktop/Pie-Chart-Separating-Values-in-Same-Row/m-p/324564...

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly