How to use an unpivot transform in Power BI Desktop (or PowerPivot) Query Editor
Part two 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 (this blog)
  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.

Transforming data using UNPIVOT

This part of the blog shows how to bring your Excel data into Power BI Desktop (or PowerPivot, if you're using that).

Loading the initial data

The first thing to do to massage the data into the right format is to load it:

Editing Excel data

Choose to load the Excel workbook, tick Sheet1 and then choose to go into Query Editor to edit the data.

Start by omitting the top row (it was a blank row in Excel):

Blank row at top

Choose to omit the top row (you'll need to enter 1 in the dialog box which appears).

Now choose to use the top row as column headers:

Column headings

The years are not data, but column headings.

Rename the left-hand column to get this:

The imported data!

The tidied-up data.

Unpivoting the data

Time now to choose the columns you want to unpivot:

Unpivot selected columns

Select the columns which run horizontally, but which you want to show as separate horizontal rows, then choose the option shown.

Power BI Desktop creates one row for each cell in your table:

Results of UNPIVOT

Forecast sales for 2018 for the North region, for example, are 28.

It's usually a good idea to rename the Attribute column to something more meaningful:

Attribute column renamed

Here I've called this column Year.

Optionally, you could now rename the query steps;

Before renaming After renaming
Before renaming After renaming

Finally, load your data into Power BI Desktoip:

Loading data

Choose to save your changes, and show the transformed data in Power BI Desktop.


A confession - I've also renamed the table from Sheet1 to Forecast:

Renaming table

You can right-click on a table to rename it in the field list window.


The next (and final) part of this blog summarises how to show this data in a stacked column chart. 

This blog has 0 threads Add post