Original subtitles

Instructor: Okay, so by this point,

you should be pretty comfortable

with the basic Query editing tools,

your date tools and text tools and number tools.

And for the most part,

they've all been pretty straightforward

and pretty intuitive,

but there are some pretty interesting,

maybe not so intuitive tools

that are available within the Query Editor.

And one thing that I want to cover now

is this idea of pivoting and unpivoting a table.

So the way I describe it,

pivoting is just a fancy way to describe the process

of turning distinct row values into columns,

which is called pivoting,

or turning columns into rows,

which is called unpivoting.

Now, it sounds pretty simple on the surface

but it's actually a little bit tricky to get the hang of,

at least it was for me.

And I'm a visual learner

and what I'm about to show you

is what finally caused it to click for me.

So imagine you've got this simple table.

You've got dates in the first column,

followed by product categories,

then region, and quantity sold.

If we were to select the Region and Quantity Sold columns

and then pivot this table,

we would essentially take the region in each row

and turn it into its own column.

So instead of having a row for North Region,

we would have a column

and the same applies for Central and South Regions too.

So what we've basically done here

is flip that table from a vertical format

up into a horizontal format.

We've turned rows into columns.

Now on the flip side,

if we started out with the table in that horizontal format

and we selected the regions in the first row,

we could unpivot to transform it into that vertical form.

Now, in other words, we're taking those regions,

which had been columns,

and transform them into rows.

So my little mental tip here

is that I imagine that the table is kind of like on a hinge

in that upper left hand corner.

Pivoting is like rotating it up from vertical to horizontal

and unpivoting is like rotating down

from horizontal to vertical.

Now, one thing to note here,

there's actually another tool in the Query Editor

called Transpose

and that works in a really similar way

but the difference is that it doesn't recognize

unique values.

So if you're dealing with a table

like the one we're looking at here,

where we have duplicate dates and quantities,

pivoting and transposing would yield very different results.

So one way to think about that

is that Transpose is kind of clunky.

It's kind of like using brute force.

It just takes an entire table

and flips it up on its side, right?

So you would have duplicates for each date

and category and so on and so forth.

So I actually have a little demo file here

that I'm gonna show you

because you really do have to see this happening

in real time

in order to kind of understand and grasp what's going on.

So let's jump over to Power BI

and we'll show you what this looks like.

All right, so we're gonna go back up

and we are gonna grab a new data set,

and there is a data set

called Product Category Sales Unpivot Demo,

and you can open this one to kind of follow along

and play around with the data set on your own,

if you're so inclined.

All right, we get our preview and we'll add this in.

Again, for the sake of the demo,

we don't really need to update the name or data types

or anything like that.

If there's any errors here, you could address those,

but everything should be good to go.

All right, so you can see here

we're actually starting off with this table pivoted

and let's say that we were given this table

by a team of sales reps that had been collecting sales data

for each of their regions by product and date

and they combined all of their work

into a single data set, right?

And they give it to you and they say,

"Hey, we need some help with some data visualization here.

And we really want to be able to filter all of our sales

by date and product and region."

And the way that this datasets configured,

it doesn't make that very easy to add a Region filter.

So as an analyst,

as someone who needs to take this data and interpret it

and analyze it and explore it,

this type of table format

can lead to a bunch of different headaches.

And really what we're looking for here

is a rectangular table

with each dimension or metric as its own column

and each observation as a row.

So ideally, what we're looking for here

is to transform this whole table

into a four-column table

that contains date, product category,

a column for region,

and then another column for my sales quantity.

Once we get it in that format,

that would give me a form

that I could then plug into my data model

and analyze in any way that I choose.

So let's take a look

at some of these pivoting and unpivoting tools

to see if they can help us.

All right, so because we're starting in this pivoted mode,

what we want to do

is we want to unpivot these columns, right?

So I'm gonna select my different regions here

and we'll come up to Transform,

and you can see we have Unpivot.

I've got a few options here,

Unpivot Columns, Unpivot Other Columns,

Unpivot Only Selected Columns.

And because we've selected the columns we want to unpivot,

we'll click Unpivot Columns, right?

And what this does is it creates an Attribute column

and then a Value column, right?

So from here, we could name this Region, right,

and this could be, you know, Quantity Sold.

So very quickly,

we've been able to get our data set in a format

that lends itself a lot better to being analyzed.

Now we could go back to the pivoted version.

All right, so we select our two columns,

we'll come up here to Pivot,

and from our dialogue,

use the names in the column Region to create new columns.

Our value column is our Quantity Sold,

so we're good there.

We'll click OK, right,

and then we're back to that pivoted version of the table.

So again, pretty straightforward demo here

when you've got a table that contains columns like this

and you need to get it into a little bit better of a format.

What we can do here

is we can delete these last applied steps here

to return the table back to its original format, right?

And the last thing that I want to show you here

is that the Transpose option

that we talked about in the slides here, right,

again, like that's this brute force option.

You take a look at our rows here, right?

We've got four different rows for July 1st,

we've got four different rows for July 2nd,

and what happens is it retains everything,

it doesn't group it.

So if we click Transpose,

you can see here that we've got four columns

for July 1st, right?

For bike, components, clothing, accessories, right?

If we scroll over, we've got four rows for the second.

Again, bike, component, clothing, accessories.

So again, using transpose in this case doesn't make sense,

'cause again it's gonna split these all

into their own columns.

And again, that's not the desired state

that we're looking for.

All right, so there you have it.

That's your demo on pivoting and unpivoting a data set.