Forum Discussion
Top 3 Ranking of multiple fields in same table
I have multiple columns/fields showing how many times a survey respondent selected a skill. For each respondent I have a row of text. I'd like to visualze the top three skills selected across all my respondents. How do I do this? The data table looks like this:
8 Replies
Thank you, some sample data is now included.
A sample of my data is attached. I will also need to be able to filter the top three according to member code (column A).
Member Code Skill: Business Skil: Communication Skill: Consulting Skill: Cultural Awareness Skill: Finance 1234 Communication 1235 1236 1237 1238 Business Consulting Cultural Awareness 1239 1240 Business 1241 Business 1242 Hi kbraga ,
Do you want to know the top 3 answers for your dataset, right ?
Just paste this code on Power Query -> Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUVIAYuf83NzSvMzkxJLM/DyoGATH6oAVmqIIokma4ZM0xydpAeQ4lRZn5qUWF8OdkldcmlOSmZcO4gBZpUWJOQqO5YlFqXBVUN2WeIw2McA0GkONIRFqjLBYohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Member Code" = _t, #"Skill: Business" = _t, #"Skil: Communication" = _t, #"Skill: Consulting" = _t, #"Skill: Cultural Awareness" = _t, #"Skill: Finance" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Member Code", Int64.Type}, {"Skill: Business", type text}, {"Skil: Communication", type text}, {"Skill: Consulting", type text}, {"Skill: Cultural Awareness", type text}, {"Skill: Finance", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Member Code"}, "Attribute", "Value"),
#"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Value", Text.Clean, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Cleaned Text","",null,Replacer.ReplaceValue,{"Value"})
in
#"Replaced Value"After that, just filter your visual for topn 3:
Ricardo
Yes, apologies, I also need to be able to filter by Organization Code. I've updated the dummy data. So, the question I need to be able to answer interactively is for each organization, what are the top three skills their members obtained. I usually use a slicer to filter by organization.
Member Code Organization Code Skill: Business Skill: Communication Skill: Consulting Skill: Cultural Awareness Skill: Finance Skill: Patience Skill: HR Skill: Meeting Skills Skill: Recruitment Skill: Computers 1234 A Communication Cultural Awareness Finance Patience HR Recruitement Computers 1235 B Business Consulting Finance Patience HR Meeting Skills Computers 1236 C Business Communication Cultural Awareness Patience HR Meeting Skills Recruitement 1237 D Communication Consulting Finance HR Meeting Skills Recruitement Computers 1238 A Business Consulting Cultural Awareness Patience Meeting Skills Recruitement Computers 1239 B Communication Cultural Awareness Finance HR Recruitement Computers 1240 C Business Consulting Finance Patience Meeting Skills Computers 1241 D Business Communication Cultural Awareness Patience HR Recruitement
