Forum Discussion
Helper IIIGrouping 2 Response into 1
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
Super UserHi 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
RLSid17Thanks 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.
- BA_Pete
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
- RLSid17
Sorry I wasn't initially clear. I wanted to group the response from what was mentioned before and calculate it like this:
Each category is 1 data source.