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:

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):

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:

The years are not data, but column headings.

Rename the left-hand column to get this:

!

The tidied-up data.

Unpivoting the data

Time now to choose the columns you want to unpivot:

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:

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:

Here I've called this column Year.

Optionally, you could now rename the query steps;

Before renaming	After renaming

Finally, load your data into Power BI Desktoip:

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:

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.