Removing duplicates and counting the number of occurrences of strings

Hello everyone, I'm new here and a novice to excel/powerquery but I can write code so I'm hoping my understanding will be good enough to find the solution to this problem.

I'm hoping someone could very kindly help me with this query as it's for a University Project and it would be really exciting if someone can help me make this work.

Here's a snippet of the sheet I'm working with:
Original sheet.png

As you can see in this Sheet I have two columns; one 'Country' column, which states a single country of origin for a person who answered a questionnaire. The second column states the number of programming languages they currently use, delimited with a ';'. All languages were selected from a checkbox and so, for example 'JavaScript' is always written exactly as 'JavaScript'.


What I'd like to achieve is to have each country in my countries list only appear once, and the number of participants who stated this was a language they have worked with as a number value in each 'language' column.

Here's a model of what I like to achieve (I just made up the data to illustrate what I'm looking for)
goal.png


I've already tried to use Formulas and also PowerQuery to acheive this but I can't figure out how to do it without a hugely manual process of creating conditional columns for every single country.. and it's a big dataset.

I've attached an extract of the original dataset (as the full version was too large of a filesize) that I'm working with to this post, in case that's useful.

If anyone can help me out I'd appreciate it so much - thank you in advance for taking the time to read!

Wow, I have a lot to learn. Thank you all for your help!