Transforming survey data - "Rank up to three in order of"

[arundaka03]

Hi everyone

I'm trying to transform a survey data question which has invited respondents to rank certain challenges in first, second and third in terms of their rank of their 'biggest challenge' 

1) There are 10 challenges they could have selected, but only up to three: "first challenge" "second challenge" and "third challenge". The data currently looks something like this: 

Response ID	Challenge - attracting talent	Challenge - Building partnerships	Challenge - competition	Challenge - cost of availability
Respondent1	First challenge		Third challenge	Second challenge
Respondent2	Third challenge	First challenge		Second challenge
Respondent3	Third challenge	Second challenge		First challenge

I want to visualize the count of each challenge by rank in Power BI, to showcase biggest and smallest challenges.

2) Between using the transpose, select, text to column and data cleanse tool, my data looks something like this: 

Response ID	Biggest challenge type	Biggest challenge rank
Respondent1	Attracting talent	First challenge
Respondent1	Building partnerships
Respondent1	Competition	Third challenge
Respondent1	Cost of availability	Second challenge
Respondent2	Attracting talent	Third challenge
Respondent2	Building partnerships	First challenge
Respondent2	Competition
Respondent2	Cost of availability	Second challenge
Respondent3	Attracting talent	Third challenge
Respondent3	Building partnerships	Second challenge
Respondent3	Competition
Respondent3	Cost of availability	First challenge

3) I've since used summarize to build a count of the rank, but now I've lost my Response ID: 

Biggest challenge	Biggest challenge rank	Count
Attracting talent	First challenge	1
Attracting talent	Second challenge	0
Attracting talent	Third challenge	2
etc

Is there anyway I can include, or append the response ID to maintain a relationship in my Power BI dashboard? Or will it have to seperate for this visualisation?

Thanks,

KA

[grchavarri]

Hi, @arundaka03 

Maybe, you could use a Summarize tool as well to concatenate the response ID. You could find the operation in the string group. See images below. 

Let me know if it helps.