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 User
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
