BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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;
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.