Grouping 2 Response into 1

[RLSid17]

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.

[BA_Pete · Accepted solution]

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

[RLSid17]

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.

[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.

[BA_Pete]

OK, gotcha.

Are all your SharePoint lists held in the same SharePoint folder?

If they are, then I would connect directly to the SP folder then use the 'Combine & Transform' function to transform and append all the different surveys together. Details on how to do that here:

https://learn.microsoft.com/en-us/power-query/connectors/sharepointfolder 

You can do the steps that I outlined previously within the Sample File transformation, so PQ will perform those transformations on every list, then append them all. The process should also retain a [Name] column after appending so you will have a reference to which SP list the data came from, therefore it follows that you can use this column to create a 'friendly' category name for each set of data in the post-appended table by creating a new custom column something like this:

if Text.Contains([Name], "balance") then "Work Life Balance"
else if Text.Contains([Name], "diversity") then "DIE - Diversity and Inclusiveness"
... etc.

Once this is done, you would create a Matrix visual in Power BI, with the following setup:

Rows: Category Friendly Name column

Columns: Fav/Neu/Unfav column

Values: Percentage calculation measure

Once you've got your data set up per above, I can help you with this matrix, but easier if I can see the column names you've chosen etc.

Pete

[RLSid17]

@BA_Pete I tried to combine and transform data but it loads a really long time. I forgot to mention that all of the lists have a different number of questions/columns. I would like to group all the responses first before I can proceed.

[BA_Pete]

Hi @RLSid17 ,

Using SharePoint as a source is always going to take a long time, I'm afraid. It's not a data warehouse, and MS actively discourages its use as such by throttling network traffic to/from it.

The number of question/answer columns doesn't matter as you'll be normalising the structure of each before they're appended. As long as they all only contain Q/A columns, and no other descriptors, this will work fine (as you'll just end up with an [Index], [Attribute], and [Value] column in each).

The other option is to do this process manually i.e. import each list one-by-one, transform each list as I've detailed one-by-one, add a hardcoded Category column to each one-by-one, then append them all together. Either way, you need to have your full dataset in one table in this normalised structure in order to create calculations from it.

Pete

[RLSid17]

Thank you @BA_Pete! Yes, they are all in the same Sharepoint folder. This might take a while but I will try to do what you suggested.