Locked 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.
Exercise files
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.xlsx17.9 KB Unpivoting Subcategorized Data - Completed.xlsx
28 KB
Quick reference
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.
Instructions
Getting started
- Create a new query to bring your data to Power Query
Unpivoting columns
- 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
- 00:04 Now, in this video, we're gonna kick our UnPivot up a notch.
- 00:08 Notice that we have actuals, budgets, and variances, but
- 00:12 we've got them for two individual months.
- 00:15 This is where we end up with subcategorized data.
- 00:17 And it can be a little bit trickier to actually UnPivot this than a regular set
- 00:21 of pivot table data.
- 00:22 The reason being is because we're only allowed one row of headers, and
- 00:27 in this case we have two.
- 00:29 In addition to that, we're looking at April.
- 00:31 It really needs to be filled across these three cells and
- 00:35 merged with these guys to get one header row.
- 00:38 So how are we gonna go about doing that?
- 00:40 Well, the first thing that we're gonna do is we're going to
- 00:43 highlight this range of data.
- 00:45 And we're gonna go and set a named range on it.
- 00:47 So I selected the range and I'm gonna quickly call this statement.
- 00:52 The reason why I want to use a named range here is because I don't want to lock this
- 00:56 down with headers or also have a column called April.
- 00:59 And then the next one's gonna be probably column three,
- 01:03 column four, then May column five, column sixth.
- 01:06 That's not really gonna work.
- 01:08 Now, that I've got a name range, I'll click outside,
- 01:10 we'll just make absolutely certain that it picks up the right cells.
- 01:13 It does, so we can go and
- 01:15 create a new query from table arrange to pull it into Power Query.
- 01:20 Now, you'll notice that my April needs to be filled across and
- 01:24 May needs to be filled across first.
- 01:26 Unfortunately, I can't right-click on a row.
- 01:30 I'm right-clicking right now and there's no row context.
- 01:33 I also can't hold down my Shift key and select two rows,
- 01:36 which makes it impossible to merge these two guys together as well.
- 01:40 So this is a little bit problematic, except that we've got a way to fix this.
- 01:47 Let's go the Transform tab and let's click Transpose.
- 01:51 Transpose flips the data 90 degrees.
- 01:54 Everything that was in row one now becomes column one.
- 01:58 Everything that was in column one now becomes row one.
- 02:01 So it's turned everything 90 degrees for us.
- 02:04 What that's done is it's actually put our April and May here so
- 02:08 I can now right-click and I can use the Fill command.
- 02:12 But I really wanted to fill April and May down.
- 02:17 I also know that I need one row for my headers.
- 02:21 Well, I can now grab column one and column two, right-click and merge columns.
- 02:30 When I do this, I don't care about the name because I'm gonna lose this anyway.
- 02:34 But I do care about the separator.
- 02:36 And what I'm gonna choose is Custom.
- 02:39 The separator I'm gonna choose is the pipe character.
- 02:41 This is Shift + \, which is usually found just the Enter key on your keyboard.
- 02:46 The reason why I use the pipe character is because it's seldom seen in real data.
- 02:51 Now if your data has pipe characters in there, make a pattern.
- 02:55 Pipe, pipe, dash or something like that that won't be confused with anything else.
- 02:59 We're gonna be looking for this later, and
- 03:01 we don't wanna cross-contaminate our data with it.
- 03:03 So as long as it's a unique character or pattern of characters, you're good.
- 03:07 The pipe, as I say, usually is unique.
- 03:10 When we say OK, you're goinna see it merges everything together, and we've got
- 03:13 a pipe, pipe, and then April pipe actual, April pipe budget, April pipe variance.
- 03:17 So this is good so far.
- 03:19 We've now got one column which, when we transpose our data back like this,
- 03:25 we now have one row that we can promote to headers.
- 03:29 But before we do that, I'm gonna replace the pipe over here,
- 03:32 right-click Replace alues.
- 03:36 We're gonna replace the pipe with the proper name for
- 03:40 my header row, which is gonna be class.
- 03:43 And we're gonna do the same thing over here on column two.
- 03:46 Replace values, we're gonna replace the pipe with a count.
- 03:51 So you start to see now why it's important to get a character or
- 03:54 a pattern of character you don't see elsewhere.
- 03:56 Because I don't wanna accidentally replace anything else in here.
- 04:00 With this done, I've now got a beautiful header row that I can go and
- 04:04 say home, use first row as headers and it'll pop them up.
- 04:08 So I've got class, account, April pipe actually, April pipe budget.
- 04:11 This'll work for as many nested levels of subheadings as you have.
- 04:15 I now wanna look at my class column.
- 04:18 Notice that revenues and expenses, there's a bunch of nulls underneath these.
- 04:21 So I wanna right-click Fill, and
- 04:23 fill them down to make sure that they're associated with every row.
- 04:27 And now the trick is how do I get rid of all of my header rows, my subtotals, and
- 04:32 my totals all at once?
- 04:33 Well, the easiest way is to actually pick on this column in this data set,
- 04:38 and say Unfilter, Null.
- 04:40 We'll get rid of it.
- 04:42 We're now down to just the individual pieces that we want.
- 04:46 I'm gonna grab class, hold down Shift, grab account.
- 04:49 These are the columns I wish to preserve.
- 04:51 Everything else is to be unpivoted.
- 04:53 So we'll right-click, Unpivot Other Columns.
- 04:57 And boom, just like that, everything gets unpivoted.
- 05:00 My final steps, grab attribute,
- 05:04 right-click, Split Column By Delimiter.
- 05:08 Powerquery is smart enough to pick up what my delimiter is.
- 05:12 And it's important that I go with each occurence here.
- 05:15 Just in case I end up with three, four, or five or six seven levels of subheadings,
- 05:19 this will pick them all up.
- 05:21 I can break it apart.
- 05:23 And now I can go back and rename my attribute one column month.
- 05:28 I can rename my attribute two column to measure.
- 05:34 Rename my value column to amount.
- 05:38 And at this point, I'm pretty much good to go.
- 05:40 I can go back and I can list this off now and call it summary or whatever I like.
- 05:46 If I want, I could even say, hey,
- 05:47 I don't need these variances cuz I could rebuild them, so I'll get rid of those.
- 05:52 At that point, I've got a beautiful,
- 05:54 clean, unpivoted data set from sub-categorized data.
- 05:58 We'll say close and load, and you can see, sure as anything, there it is.
- 06:03 So as long as I expand my named range when the statement actually expands,
- 06:07 this will work beautifully to actually unpivot an entire year
- 06:10 with multiple levels of nested subheadings, should I end up with that.
Lesson notes are only available for subscribers.