BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
If you get data in pivot (two-dimensional) form, you can unpivot it to get the underlying rows, and hence produce charts and visuals. This blog shows how!
- Unpivoting data in Query Editor: a case study (this blog)
- Transforming data using UNPIVOT
- Creating a chart based upon the transformed data
Posted by Andy Brown on 04 January 2018
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Unpivoting data in Query Editor: a case study
This blog shows starts with a simple spreadsheet of data in Excel:

The source data for this case study.
I'll show how you can load this into Query Editor (you could use either PowerPivot or Power BI Desktop; for this blog we'll use the latter), and then transform it to get this:

We'll apply an unpivot transform to generate one row for each value in the Excel spreadsheet.
Finally, I'll use this data to derive the following chart:

I'll show (in summary) how to create this stacked column chart.
Let's start with importing the data!
You can download the file yourself if you want to follow along!
- Unpivoting data in Query Editor: a case study (this blog)
- Transforming data using UNPIVOT
- Creating a chart based upon the transformed data