Power BI has a very useful feature in the Power Query Editor named Unpivot Columns. In this blog entry, we’ll look at an interesting problem with analyzing Human Resources (HR) peer Performance Review survey results, and how to solve that problem using the Unpivot Columns feature.
Pivot Data and the Problem
The term Pivot Data, in this context, refers to metric data (i.e. Survey Results) that have been broken into individual columns/values and line-items in a report. The metrics here are peer survey results which rate coworkers on values of 1-5 for categories and questions around: core competencies, overall achievement of committed goals, displays of leadership qualities, etc. This is a specific example, but you’ll see how this can easily apply to many different types of surveys.
In the sample above, we’re looking at a report delivered to Human Resources which lists Peer Performance Review ratings by category as individual submitted survey results. This is very much a common report/view into survey results regardless of the subject matter.
With this data, if we try to generate a Power BI report on a complete summation of points across all categories, it would be complicated at best. Instead, we’re kind of stuck in a position where we can only get summaries by individual category. For example:
The report above is useful (and in the final solution, we’ll still be able to get to this per-category level comparison), but how could we instead get a report that compares average-totals across the sum of all categories’ ratings? By using the Power Query Editor Unpivot Columns feature, that’s how.
Power BI – Power Query Editor – Unpivot Columns
Things get a bit wordy when trying to explain Unpivot in writing, so let’s look at the following video instead:
Here’s a summary of what was shown in the above video:
- A quick review of the existing report and the core issue we’re trying to get around: report averages can only be generated on a category-by-category basis.
- Selecting the columns we don’t want to Unpivot (Employee and Survey Date) and using Unpivot Columns to break down the survey results into their unique category attribute-value pairs.
- Changing the column header values to make the report title and description a bit friendlier.
- Showing how our new aggregate column “Rating” can be viewed as a holistic “total points” against which averages can be evaluated.
- Finally, providing an example of how there really is no lost functionality from the original report. We can add a quick Slicer control to get back to category-specific averages if needed.
Conclusion
Survey results data are often delivered in reports that provide a line-by-line breakdown of each submitted response. If that data is imported into Power BI, and wrangled in its default format, then we can’t really get at a report which helps us understand the summary of result totals across all categories/questions/etc. in the survey. To get around that problem, we can use the Unpivot Columns feature in the Power Query Editor, and if needed…we can still get back to the original summaries with the Slicer control.
Categories: Business, Business Management, Human Resources, Power BI
Leave a Reply