This post follows Dynamic Qualtrics Survey for Peer Evaluation (Part 1) where we looked at using Qualtrics features to make surveys dynamic and data-driven. Here we’ll dive into features from Excel that enable us to transform and reshape our survey data into formats more friendly to review and analysis.
Peer Evaluation Review
Recall that our survey contains a single question that allows members on a team to rank the relative contributions of their teammates toward some project or milestone. When we download the results from Qualtrics, we receive a data table with one row per reviewer and columns for their scores of each person reviewed (we are including self-reviews). We are going to look at ways to flip this relationship–one row per person reviewed with individual scores and aggregate statistics across the columns. Fundamentally we will be pivoting data, along with a few other transformations.
Grouping and Pivot Tables
Pivots and grouping allow us to reshape our data, usually changing the number of rows and columns. One common use is to report aggregate statistics, grouped by some data values. Imagine a table of hundreds of 1-5 star product reviews (maybe with categories, data about raters, etc.). Using pivot and grouping, you could count the number of 1,2,3,4, and 5 star reviews. You could calculate average review scores by product category or reviewer demographics. In all these cases, we start with data in one shape (number of rows and columns) and end up with another shape that might include illustrative statistics.
Pivot functions are available in lots of software packages, such as Python Pandas and R Tidyr. Excel also supports both simple and more complex pivoting using its built-in Power Query environment. We’ll use Excel Power Query here to accomplish what we need with very minimal coding.
Power Query
Let’s dive in to our Qualtrics data to see how Power Query works. Here’s the data. (I hid some columns to help fit on the page. I also removed two of the three standard Qualtrics headers.)
1. Data Table
To work in Power Query, your data source must be a Data Table. Creating a table is a simple as selecting your data then clicking Data > From Table/Range (see here). I selected only the columns from Q1_1 though Team. If we cared about treating self-reviews differently, we might want to keep the recipient data. Here’s the data, now in the Query editor:
Right now our Query has just one step: Changed Type. That’s the table selection step we just completed. Each additional step in the transformation will appear below Changed Type. At any point we will be able to step back and forth through the steps to check our work.
2. Unpivot Scores
For our first real transformation step, we want to “unpivot” the scores. This style of pivot will add rows while reducing columns. At the end of the operation we will have a row for every individual score, rather than 4-5 scores on every row like we have now. Start by selecting the five score columns, then click Transform > Unpivot. The individual scores are now in a column named Value, and the question number in Attribute.
3. Add Custom Column
There is no avoiding having to write a little code at this stage. It’s simple, but necessary. The goal in this step is to identify the name of the person being rated on each row. You’ll notice that the score is only identified by question number in the Attribute column. And each row contains every person’s name on the team. What we need to do is select the name indexed by the question, e.g., Q1_1 = TeamMember1’s name, Q1_2 = TeamMember2’s name, and so on.
Click the Add Column tab then click Custom Column. Label the new column “Name” and paste in the following code (adapt as needed to your data):
if [Attribute] = "Q1_1" then [TeamMember1] else if [Attribute] = "Q1_2" then [TeamMember2] else if [Attribute] = "Q1_3" then [TeamMember3] else if [Attribute] = "Q1_4" then [TeamMember4] else [TeamMember5]
Click OK and you should see your new column.
4. Rename
For the sake of clarity, let’s rename the generic Value column to something more descriptive, like “Rating.” Double click the word Value in the table header and you can type in the new name.
5. Group Rows
Ultimately, we will need to reduce the number of rows again so that we have one row per person (per group–a name may appear in more than one group), with all of their group peer’s scores. To do that select the Group and Name columns, then click Transform > Group By. The group by window will probably include a Count aggregate by default. You can keep that or remove it.
Now add the aggregate statistics that you will want in your final report. We’ll add average here by clicking Add aggregation, name the column “Average,” select Average from Operation, and select Rating from Column. If you want other columns, e.g. Minimum score, or Maximum score, you can add those, too.
Because we also want to display all of the original scores, we will need to include the Operation All Rows. Give that column the name “AllRatings.” Click OK.
6. Expand All Ratings
Our query table is getting close to the final format. But you’ll notice that the AllRatings column contains an mini embedded table.
We want to expand those to display individual ratings on each row. Again, we will need to add a little code to the process. See the bar above the green-tinted table? That contains M Code. M is the language of Power Query. When we click buttons to perform operations, Excel is generating this code for us.
Now we need to edit that code a little bit. What we want is a index number on each row of those mini tables (1,2,3,etc.). To do that, expand the code bar, and look for the AllRatings definition.
Where you see the word “each” followed by an underscore, replace the underscore with this line of code:
Table.AddIndexColumn(_, "RaterNumber", 1, 1)
The M code should now look like this:
Finally, we can expand the embedded tables. On the AllRatings column header click Expand. Select Rating and RaterNumber, uncheck Use original column name as prefix.
Note: sometimes the RaterNumber column isn’t appearing as an expansion option. If you don’t see it, just select Rating, click OK, then replace the M code with the following:
Table.ExpandTableColumn(#"Grouped Rows", "AllRatings", {"Rating", "RaterNumber"}, {"Rating", "RaterNumber"})
7. Pivot Columns
Almost there! Our rows count has expanded again, so we’ll need to do a final pivot. You may have been wondering why we went to all the trouble of adding that RaterNumber column. It’s the key to this pivot. Each index number will become a column header under which the individual scores will be reported.
Pivoting with the data as-is will result in columns named 1, 2, 3, 5, and 5. That’s a bit ugly. So let’s create a column with better header names. Just like step 3, click the Add Column tab then click Custom Column. Label the new column “RaterLabel” and paste in the following code:
"Rater" & Text.From([RaterNumber])
Make sure that you remove the RaterNumber column now that we have RaterLabel.
Now, select the RaterLabel column then click Transform > Pivot Column. Choose Rating for the Values Column. Click OK.
Here’s a look at the query editor after all of the above steps.
Final Data Table
After saving and closing the Query editor, your data table output should look something like the one below.
What if you collect new ratings? Just add more rows to the source table, then go to the output table and click Query > Refresh. All the query work you did is reusable.
After clicking refresh, the new ratings are included:
For projects with frequent updates and a need for real-time reporting, this may present some friction points. We are looking into more automated integrations.
If you followed along this far, I recommend customizing your report with more aggregate statistics as an exercise.
Appendix: Full M Code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{
{"Q1_1", Int64.Type},
{"Q1_2", Int64.Type},
{"Q1_3", Int64.Type},
{"Q1_4", Int64.Type},
{"Q1_5", Int64.Type},
{"TeamMember1", type text},
{"TeamMember2", type text},
{"TeamMember3", type text},
{"TeamMember4", type text},
{"TeamMember5", type text},
{"TeamCount", Int64.Type},
{"Team", type text}
}
),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(
#"Changed Type",
{"TeamMember1", "TeamMember2", "TeamMember3", "TeamMember4", "TeamMember5", "TeamCount", "Team"},
"Attribute",
"Value"
),
#"Added Custom" = Table.AddColumn(
#"Unpivoted Columns",
"Name",
each
if [Attribute] = "Q1_1" then [TeamMember1]
else if [Attribute] = "Q1_2" then [TeamMember2]
else if [Attribute] = "Q1_3" then [TeamMember3]
else if [Attribute] = "Q1_4" then [TeamMember4]
else [TeamMember5]
),
#"Renamed Columns" = Table.RenameColumns(
#"Added Custom",
{{"Value", "Rating"}}
),
#"Grouped Rows" = Table.Group(
#"Renamed Columns",
{"Team", "Name"},
{
{"Count", each Table.RowCount(_), Int64.Type},
{"Average", each List.Average([Rating]), type number},
{
"AllRatings",
each Table.AddIndexColumn(_, "RaterNumber", 1, 1),
type table [
TeamMember1=nullable text,
TeamMember2=nullable text,
TeamMember3=nullable text,
TeamMember4=nullable text,
TeamMember5=nullable text,
TeamCount=nullable number,
Team=nullable text,
Attribute=text,
Rating=number,
Name=text
]
}
}
),
#"Expanded AllRatings" = Table.ExpandTableColumn(
#"Grouped Rows",
"AllRatings",
{"Rating", "RaterNumber"},
{"Rating", "RaterNumber"}
),
#"Added Custom1" = Table.AddColumn(
#"Expanded AllRatings",
"RaterLabel",
each "Rater" & Text.From([RaterNumber])
),
#"Removed Columns" = Table.RemoveColumns(
#"Added Custom1",
{"RaterNumber"}
),
#"Pivoted Column" = Table.Pivot(
#"Removed Columns",
List.Distinct(#"Removed Columns"[RaterLabel]),
"RaterLabel",
"Rating",
List.Sum
)
in
#"Pivoted Column"