Relate Programming Language Data to Country of Origin in a dataset

[danielTheNewbie]

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:

Country	LanguageHaveWorkedWith
Slovakia	C++;HTML/CSS;JavaScript;Objective-C;PHP;Swift
Netherlands	JavaScript;Python
Russian Federation	Assembly;C;Python;R;Rust
Austria	JavaScript;TypeScript
United Kingdom of Great Britain and Northern Ireland	Bash/Shell;HTML/CSS;Python;SQL
United States of America	C;C#;C++;HTML/CSS;Java;JavaScript;Node.js;PowerShell;Python;SQL;Swift
United States of America	HTML/CSS;JavaScript
Malaysia	HTML/CSS;JavaScript;PHP;Ruby;SQL;TypeScript
India	HTML/CSS;JavaScript
Sweden	C++;Python
United Kingdom of Great Britain and Northern Ireland	C#;TypeScript
Spain	Bash/Shell;HTML/CSS;JavaScript;Node.js;SQL;TypeScript
Germany	C;C++;Java;Perl;Ruby
Peru	C;C++;JavaScript;Python;R;SQL
United Kingdom of Great Britain and Northern Ireland	JavaScript;SQL
United States of America	Bash/Shell;Python
Turkey	C#;HTML/CSS;Java;JavaScript;Node.js
Canada	Bash/Shell;HTML/CSS;JavaScript;PHP;Ruby;SQL

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 acheive 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 acheive (I just made up the data to illustrate what I'm looking for)

Country	HTML/CSS	Python	JavaScript	Go	Java	C	C++	C#	...
Belarus	343	64	634	213	246	426	642	264
Moldova	4352	24	2425	64	6457	8096	9657	5
Lithuania	579	759	6548	274	737	74	48	67
…

I've already tried to use 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.

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

[Daryl-Lynch-Bzy · Accepted solution]

Hi @danielTheNewbie - you can start and end in Power Query by using Split Column by Delimiter into new Rows:

The resulting rable can be loaded into the Data Model.  After adding a simple measure:

Count Rows = COUNTROWS( 'Table Name' )

you can create the following table:

If you want this Power Query you can just the Pivot function.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVLBcsIgFPwVhh619QM4aWaqtmpTsSfHw9M8GzQBB4gOf18kzojW1LY3EvYt+3Z3Pqe8UHvYCqBtmrRabDAbjzoJ5+wF9sBXWuwse1tucGXFHh8Tlg5Sxg9ibemiPacTtDnqAmRm/Hw0kjqbKxkw08oYAZI8Y4YarPC/27RrDJbLwrHkBGVT5oE1a9cfdFAUMc7cDutjwHxIYTEjr0J+Zqokak36GsGSnhYWhCReEpkofZQnyVDjUaMn7IHJOzzHojhvehLA30cxM7dg0RyJuyVqsQoGseSBfXMptmqiMnzaGJaqA+r6nTN9ZNwPj9xIIIyMoQBnRAMkBDOtli48dGXWUGZNc+GeH3w28tSAKLp/muxNuhLAdx7eYP8N926s0EddgnR1CF5lMD713QtLB4j/qi7uL7roC3aV8F/Xiih/0ZVo1cjRWaW36GqP7pUoDCQgIYP71sXx08XiCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, LanguageHaveWorkedWith = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"LanguageHaveWorkedWith", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"LanguageHaveWorkedWith", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "LanguageHaveWorkedWith"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"LanguageHaveWorkedWith", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"LanguageHaveWorkedWith", "Language"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Count", each 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Language]), "Language", "Count", List.Sum)
in
    #"Pivoted Column"