Top 3 Ranking of multiple fields in same table

[kbraga]

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:

[kbraga]

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

[camargos88]

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

[kbraga]

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

[camargos88]

Hi @kbraga ,

The code I sent you should work. Just select the columns member and organization and unpivot all other columns, if you click with right mouse buttom on them you will see this option.

I hope it helps,

Ricardo

[kbraga]

If I have other data in the table, would I need to add this to the same table or creat a new one? 

[camargos88]

If you wanna do it on the same table, just select all the columns you don't need to unpivot. Also, you can select the columns you want to unpivot, just the opposite.

The first option seems more apropriate for you if you can add (more answers/questions) or change the column labels.

Ricardo

[camargos88]

Hi @kbraga ,

Can you provide a sample of your data pasted here ?

Ricardo

[kbraga]

Thank you, some sample data is now included.