Unpivoting Subcategorized Data

Quick reference

• 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.