Get and Transform
The “Get and Transform” tools have been widely available since Excel 2016. Prior to 2016, the tools were available via a free download as part of the Power Query toolset.
The Problem
Our dataset contains sales for a year grouped by month (blue), and each row represents an App/Type grouping (yellow).
We want to transpose the monthly data and combine it with the App/Type data. Our result should have
- 12 rows for “WenCaL/Sales” (Jan…Dec)
- 12 rows for “WenCaL/Volume” (Jan…Dec)
- 12 rows for “WenCaL/Returns” (Jan…Dec), etc.
Problem with Blanks
One of the issues with traditional transpose strategies is wherever an empty cell exists, no row will be created for that App/Type combination.
To solve this issue, we will need to “zero fill” all the empty cells in the range of numbers.
Step 1 – Pull the Data into Power Query
The Power Query suite of tools (located in the “Get and Transform” group on the Data tab) will allow us to quickly and easily correct this data.
Click anywhere in the data (anywhere in A3:N12) and select Data (tab) -> Get & Transform Data (group) -> From Table/Range.
Provided there are no completely blank rows or columns in the data, the Create Table dialog box’s selection range should be correct. If not, make the necessary adjustments. Also, make sure the “My table has headers” option is selected then click OK.
The data will be sent to the Power Query Editor for further processing.
The Power Query Editor is an entire suite of tools used to fix virtually any data irregularity or data transformation issue.
The first thing we will do is to select the Name field from the Query Settings -> Properties panel on the right and change the name to something more meaningful, like “TableFinal”.
Featured Course
Master Excel Power Query – Beginner to Pro
Step 2 – Fix the Data
Our first task will be to replace all the empty value cells with zeroes. Empty cells will be skipped in the next step when we transpose the table. Since we don’t want chronological gaps in our data, we need to occupy the empty cells with a value.
NOTE: In Power Query, an empty cell is labeled as a “null” cell. This is just a fancy way to say “empty” in Power Query.
We have 12 columns of monthly data to replace nulls with zeroes.
Click the header of the “Jan” column, scroll to the right, hold CTRL then click the header of the “Dec” column.
With the 12 month columns highlighted, select Home (tab) -> Transform (group) -> Replace Values.
In the Replace Values dialog box, type “null” (no quotation marks) in the Value To Find field, and a “0” (no quotation marks) in the Replace With field and click OK.
All the “null” cells have been replaced with zeroes.
Step 3 – Transposing the Months
The next step is to transpose the month columns and create associated entries for each corresponding App/Type combination.
We could select the month columns like we did earlier and then transpose them but imagine if instead of 12 month columns there were 1,000 product columns.
A faster method would be to select the two columns that we will be reproducing, the App and Type columns, and then instruct Power Query to fix all the OTHER columns, leaving our selected columns out of the process.
Select the App and Type columns, then select Transform (tab)-> Any Column (group) -> click the small arrow next to Unpivot Columns -> Unpivot Other Columns.
The data has been transposed.
Step 4 – Loading the Results into Excel
To load the results into Excel, select Home (tab) -> Close (group)-> Close & Load.
We have our transposed list in Excel as an official Excel Table.
We can now create Pivot Tables, Charts, filter, and sort to our heart’s delight.
Related Tutorials
Change Horizontal Data to Vertical
Unstack Data from One Column to Many Columns
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
