563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
How to use an unpivot transform in Power BI Desktop 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!
This part of the blog shows how to bring your Excel data into Power BI Desktop (or PowerPivot, if you're using that).
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.
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.
|Parts of this blog|
25 Aytoun Street