Forum Discussion
Grouping 2 Response into 1
Hi,
I was hoping someone can assist me with the project I'm working on as I have no experience working with Power BI. I need to be able to group the survey response like the below:
Favorable = Agree and Strongly Agree
Neutral = Neutral and I Don’t Know
Unfavorable = Disagree and Strongly Disagree
This is the sample data: (each column is a question and then the rest are responses)
And also would it be possible to create a dashboard with multiple data sources? Since I have a different set of survey questions.
Hi RLSid17 ,
I'm not sure where you're up to with regards importing the data into Power Query but, once you've got it in PQ, I would do the following:
1) Unpivot the question columns into two single columns: question and response.
Multi-select (Ctrl+click) all the columns that are not the question/answer columns.
Go to the Transform tab > Unpivot columns (dropdown) > Unpivot Other Columns. This will give you two new columns: [Attribute] (Question), and [Value] (Answer).
2) Now go to the Add Column tab > Custom Column, and use a calculation something like this:
if List.Contains({"Agree", "Strongly Agree"}, [Value]) then "Favorable" else if List.Contains({"Neutral", "I Don't Know"}, [Value]) then "Neutral" else if List.Contains({"Disagree", "Strongly Disagree"}, [Value]) then "Unfavorable" else // add you escape value here e.g. null, "Error", "Unknown Response" etc.You now have everything you need in the optimal structure to report on it in Power BI.
Regarding using multiple sources: Yes, you can report across as many as you like (within reason), but you'll need to be able to relate them to one another if you want them to all work in sync. Without a LOT more detail around your different sources, their contents and structures, and what you intend to do with the output, I can't really give any further advice on this.
Pete
8 Replies
Hi RLSid17 ,
I'm not sure where you're up to with regards importing the data into Power Query but, once you've got it in PQ, I would do the following:
1) Unpivot the question columns into two single columns: question and response.
Multi-select (Ctrl+click) all the columns that are not the question/answer columns.
Go to the Transform tab > Unpivot columns (dropdown) > Unpivot Other Columns. This will give you two new columns: [Attribute] (Question), and [Value] (Answer).
2) Now go to the Add Column tab > Custom Column, and use a calculation something like this:
if List.Contains({"Agree", "Strongly Agree"}, [Value]) then "Favorable" else if List.Contains({"Neutral", "I Don't Know"}, [Value]) then "Neutral" else if List.Contains({"Disagree", "Strongly Disagree"}, [Value]) then "Unfavorable" else // add you escape value here e.g. null, "Error", "Unknown Response" etc.You now have everything you need in the optimal structure to report on it in Power BI.
Regarding using multiple sources: Yes, you can report across as many as you like (within reason), but you'll need to be able to relate them to one another if you want them to all work in sync. Without a LOT more detail around your different sources, their contents and structures, and what you intend to do with the output, I can't really give any further advice on this.
Pete
Thanks for your input! This is my first time trying Power Query.
For the unpivot how do I do that? Since as I've mentioned all the columns are questions. The source is from Sharepoint list so each question columns are of Choice type.
Hi RLSid17 ,
If you don't have a column with Questionnaire Number, or Respondent Name or similar, then you can add an Index column to your data to act as a unique row key:
Go to the Add Column tab > Index Column (dropdown) > From 1. You can now select your [Index] column before you do the 'Unpivot Other Columns' bit.
However, I'm not sure how you're planning to analyse your data without any further information, other than the questions and answers.
Pete
