How to use an unpivot transform in Power BI Desktop (or PowerPivot) Query Editor
Part one of a three-part series of blogs

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!

  1. Unpivoting data in Query Editor: a case study (this blog)
  2. Transforming data using UNPIVOT
  3. 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:

Excel data

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:

Unpivoted data

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:

Final 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!

  1. Unpivoting data in Query Editor: a case study (this blog)
  2. Transforming data using UNPIVOT
  3. Creating a chart based upon the transformed data
This blog has 0 threads Add post