Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
The challenge with the Unpivot functionality is that you can only unpivot based on a single header row. So what if you have multiple rows of subheaders? In this lesson we will show you how to circumvent that issue.
Download this lesson’s related exercise files. You can download source data files for the course from the resources section of your Lessons page.Unpivoting Subcategorized Data.xlsx
17.9 KB Unpivoting Subcategorized Data - Completed.xlsx
Unpivoting Subcategorized Data
An overview of unpivoting data containing subcategories.
When to use
When you need to unpivot some columns of data into rows, but the data set contains subcategories.
- Create a new query to bring your data to Power Query
- Demote headers if they were part of the data source
- Transpose the table so that the Category and Subcategory rows become columns
- In the Category and Subcategory columns, fill data up or down as needed
- Select the Category and Subcategory columns and merge the columns with a unique delimiter
- Transpose the data back to the original orientation
- Promote the first row to headers
- Highlight the column(s) that you do NOT want to unpivot
- Right click one of the column headers --> Unpivot Other Columns
- Right click the unpivoted header column --> Split Column --> by Delimiter
- Enter the unique character you chose above
- Set to split repeatedly
- Rename the Attribute columns appropriately
Finishing the query
- Apply further transformations as required
- Set data type for each column
- Rename the query
- Close & Load the query
Hints & tips
- The Transpose function flips the entire table 90 degrees, so rows become column and columns become rows
- This technique will work for as many subcategories as you have in your data
Lesson notes are only available for subscribers.