Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kbraga
Helper I
Helper I

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:

 

top 3 snip.JPG

 

8 REPLIES 8
kbraga
Helper I
Helper I

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 CodeSkill: BusinessSkil: CommunicationSkill: ConsultingSkill: Cultural AwarenessSkill: Finance
1234 Communication   
1235     
1236     
1237     
1238Business ConsultingCultural Awareness 
1239     
1240Business    
1241Business    
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:

 

Capture.PNG

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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 CodeOrganization CodeSkill: BusinessSkill: CommunicationSkill: ConsultingSkill: Cultural AwarenessSkill: FinanceSkill: PatienceSkill: HRSkill: Meeting Skills Skill: RecruitmentSkill: Computers
1234A Communication Cultural AwarenessFinancePatienceHR RecruitementComputers
1235BBusiness Consulting FinancePatienceHRMeeting Skills Computers
1236CBusinessCommunication Cultural Awareness PatienceHRMeeting SkillsRecruitement 
1237D CommunicationConsulting Finance HRMeeting SkillsRecruitementComputers
1238ABusiness ConsultingCultural Awareness Patience Meeting SkillsRecruitementComputers
1239B Communication Cultural AwarenessFinance HR RecruitementComputers
1240CBusiness Consulting FinancePatience Meeting Skills Computers
1241DBusinessCommunication Cultural Awareness PatienceHR 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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



camargos88
Community Champion
Community Champion

Hi @kbraga ,

 

Can you provide a sample of your data pasted here ?

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Thank you, some sample data is now included. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.