Forum Discussion
Top 3 Ranking of multiple fields in same table
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 | ||||
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
If I have other data in the table, would I need to add this to the same table or creat a new one?
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