This post is the first in a series of posts that will walk you through one of the coolest data transformation features in Excel. If you are not familiar with Power Query Add-in for Excel, or never had the time or energy to start learning it, this series of posts will convince you to start.
But let’s start with the big and common challenge. As an advanced Excel user, you prefer to analyze or create your reports with PivotTables and PivotCharts. Unfortunately, many of your colleagues are not so familiar with it, and prefer to share with you their static tabular data, or summaries. You get their static tables, but cannot convert it into PivotTable and shape it to meet your analysis needs.
Can you transform a static pivoted table into a PivotTable?
Another common scenario – You have an Excel workbook that is used by your team to report project progress.
As seen in the screenshot above, each member reports his weekly progress in the left table. Though it is easy to use this table for quick data entry (In this case: hours of work), it is quite challenging to start data analysis on such table.
Sometimes you wish you had the data in a format that a PivotTable will digest. Well, now you can 🙂
With Power Query technology in Excel, you can access your tables, perform a transformation, which we callUnpivot, and create a PivotTable from the transformed data.
Before we start, if you are looking for more resources on Unpivot functionality, go to Office Support Page here, or read more on Ken Puls (Excelguru) blog here, If you are familiar with other blogs that showcase Unpivot, please share with us by commenting below. I will mention more posts as we go along in this series. We have plenty of things to show you.
In this post we will start with the first example above, and move towards more complicated tables and advanced scenarios in the following posts. By the end you will be able to transform any type of table into a PivotTable.
Note that the instructions are based on the Excel 2016 user interface, but you can get the same results with the Power Query Add-in on earlier versions of Excel.
Open the workbook here, select one of the cells in Table1, in the Data tab, click From Table (in the new Get & Transform group).
The Query Editor will open. This is where you can do many transformations on your data before it lands in your workbook.
Right click on the first column header, and click Unpivot Other Columns.
You will notice that all the year columns (that were not selected in the previous step) were transformed into two columns: Attribute, which contains the year information, and Value, which contains the numeric cells information. Now the structure of the table in the Query Editor fits to a format that can be pivoted.
Let’s just rename the column Attribute and Value to Year and Sales. Renaming columns in the Query Editor is easy. Pretty similar to renaming files in Windows. Click on each of the headers and type the new name.
Still in the Query Editor window, click Close & Load. The transformed table will be loaded to a new worksheet.
Select one of the cells of the new table, and click PivotTable in Insert tab.
That’s it for today. Stay tuned for more Unpivot transformations, tips and tricks on my next posts.
