How to use an unpivot transform in Power BI Desktop (or PowerPivot) Query Editor
Part three 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
  2. Transforming data using UNPIVOT
  3. Creating a chart based upon the transformed data (this blog)

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.

Creating a chart based upon the transformed data

This blog assumes that you know how to use Power BI Desktop, and just gives the summary steps to follow to create our chart.  First choose the stacked column visual:

Stacked column visual

Choose this visual.

 

Choose fields to show in your chart:

Fields to show

Either drag the fields into the field well, or select them in the list.

 

You should now be looking at a chart like this:

Basic chart

A basic stacked column chart.

 

I then changed the Legend and Title properties, among others:

Legend properties Title properties
Legend properties Title properties

I've also added a Y axis title and a border to the visual to get the final chart:

The final chart

The final chart, in all its glory!

 

A final thought: if you could only get your IT people to provide the data in its raw form, you wouldn't have to unpivot it!

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