Forum Discussion
Top 3 Ranking of multiple fields in same table
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 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?