Pivoting Data

Quick reference

• 00:04 In this video we're gonna look at two examples for pivoting data.
• 00:08 One, where we want to aggregate the values and another where we don't.
• 00:13 We're gonna start with this example, which is the raw transactions behind an accounts
• 00:18 receivable transaction aging list.
• 00:20 And we're gonna start by pulling that data into power query from table arranged.
• 00:26 We have our big long list and what we really want is we
• 00:29 want customers down the left hand side and we're gonna use the days outstanding for
• 00:34 the number of columns and then we'd like to aggregate the value.
• 00:37 So to do that, we'll grab the value or the column that we want to use as column
• 00:42 headings and we'll gonna go to transform and we'll say Pivot Column.
• 00:48 When we choose Pivot Column, it decides to use the selected column for
• 00:52 the names of your new columns.
• 00:55 And then it says all right, that's cool, which values would you like to aggregate?
• 01:00 Well, we don't really wanna aggregate the customer column because that's all taxed.
• 01:04 What we want to aggregate is the amount column, so
• 01:06 we'll just change this to say Amount.
• 01:08 And at this point, just like a regular standard Excel Pivot table,
• 01:13 it completely pivots it and sums up everything that we need.
• 01:17 So that's pretty easy.
• 01:18 So I'm going ti go on and mark this guy down now and just call him AR Listing,
• 01:22 and we'll now go on and say home closing load two.
• 01:27 And I'm gonna load this right here onto the same existing worksheet.
• 01:30 And we're gonna drop it right here into F3.
• 01:35 And you can see that the values are here if anybody were to add new rows
• 01:39 to this particular table, all we need to do is do a Data Refresh All and
• 01:43 it's gonna refresh and bring in all of the new values and keep this up to date.
• 01:47 So pretty similar to a pivot table actually in a lot of ways.
• 01:52 Now let's go look at example two because this one is a little bit different.
• 01:55 This is were we actually have the ability to pivot text.
• 02:00 What I'd like to do here is, I've got a listing of a few different customers and
• 02:04 then I have the line ID of their actual invoice as well as what they purchased.
• 02:10 And, what I'd like to see in this case is I'd like to have a number of columns for
• 02:14 the each individual item that they purchased and
• 02:17 I'd like to see the text in that bottom right hand corner area.
• 02:21 Now, if you've worked pivot tables a lot, you know that pivot tables
• 02:24 really don't work well by trying to put text into the bottom right hand corner,
• 02:28 into the values area.
• 02:30 When you put text in there It counts it and that's not all we want here.
• 02:34 We want to preserve this detail.
• 02:36 So let's go take a look.
• 02:38 We'll hold this data in from table arrange as well.
• 02:43 Here we are in Power Query so we need to make our decision.
• 02:45 Which column are we gonna use for headings?
• 02:47 We're going to choose the line ID.
• 02:50 So I'm going to go to transform and I'm going to use Pivot column.
• 02:55 So no worries on that, it's gonna use the line id in order to create new columns and
• 03:00 then it says what's the values columns that we want to use.
• 03:02 And say well you know what?
• 03:04 I want to use the Product purchase, I'd like to see this information.
• 03:09 And here's the challenge, if I say okay, what I'm gonna get is I'm gonna
• 03:13 get a count of each of these items, and that's not really going to do so well, so
• 03:17 let's say okay, we'll show you, so we get one, one, one, one, zero.
• 03:21 Well great, I can see how many were bought for each thing, but
• 03:24 that's not what I want.
• 03:26 So if I go into the gear icon, I can actually reconfigure this step and
• 03:31 I can open up the advanced options that hidden in this little white triangle.
• 03:35 And you'll notice that now it says what's the aggregate value function.
• 03:39 Say well, its a count all, but I don't really want a count.
• 03:43 What I actually want is something different.
• 03:46 Notice we've got a count of not blanks, minimum, maximums and medians, but
• 03:50 the most intriguing one, is this one a the bottom.
• 03:52 Don't aggregate.
• 03:54 And this one actually turns out to be super, super useful.
• 03:59 When we say don't aggregate, and we say okay, look what happens.
• 04:04 How cool is that this actually brings back all of the individual pieces of text
• 04:08 without counting or summarizing them or trying to do anything weird to them.
• 04:12 It will actually give me all the data that I actually readily have.
• 04:16 So I can now go back and say hey this is cool let's go call this,
• 04:20 invoice line items.
• 04:24 Because now I can actually rebuild what the invoice looked like.
• 04:28 So at this point, I can go and say, home, close and
• 04:31 load, we're going to chose again close and load two.
• 04:33 And I'll drop this guy again on the same worksheet.
• 04:36 Existing worksheet, and we'll go and put it right here and F3 and say Okay.
• 04:44 And look at that, nicely pivoted.
• 04:47 And, of course, once again, if Hector Fleming here goes and buys another item,
• 04:52 we'll put on number two here and add that he's bought a dishwasher.
• 04:57 I can now see, here's Hector Flemming right here, and if I right click and
• 05:00 refresh this table,
• 05:02 you can see that we actually end up adding that to his items that he's purchased.
• 05:06 So pretty cool things.
• 05:07 Pivoting is a great little feature, but in this case one of their coolest little
• 05:12 hidden gems is that advanced section to be able to choose to
• 05:16 override the aggregation, so you don´t actually have to have just a count or sum.

Lesson notes are only available for subscribers.