Forum Discussion
Relate Programming Language Data to Country of Origin in a dataset
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!
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"
1 Reply
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"