Locked lesson.
About this lesson
While we can use PivotTables to pivot data, occasionally we need to pivot our data in the data preparation phase. This lesson exposes how the Pivot functionality works in Power Query.
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.
Pivoting Data.xlsx22.7 KB Pivoting Data - Completed.xlsx
30.4 KB
Quick reference
Pivoting Data
An overview of pivoting data in a table.
When to use
When you need to pivot your data before landing it in a worksheet.
Instructions
Getting started
- Create a new query to bring your data to Power Query
Pivoting while aggregating values
- Highlight the column that you want to use for new column headings
- Go to Transform --> Pivot Column
- Choose the column that contains the data you would like to aggregate --> OK
Pivoting without aggregating (preserving original values)
- Highlight the column that you want to use for new column headings
- Go to Transform --> Pivot Column
- Choose the column that contains the data you would like to see in the values area
- Select Advanced Options --> Don’t Aggregate --> OK
Finishing the query
- Apply transformations as required
- Set data type for each column
- Rename the query
- Close & Load the query
Hints & tips
- The Aggregate Value Functions available in the Advanced Options menu include:
- Count all records (the default function)
- Count all non-blank records
- Minimum value
- Maximum value
- Median value
- 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.