All language subtitles for 11. Power BI Demo Pivoting & Unpivoting Data

OK so by this point you should be pretty comfortable with the basic query editing tools date tools your

text tools your number tools and for the most part they've all been pretty straightforward pretty intuitive

to use.

But there are some pretty interesting maybe not so intuitive tools available in that query editor as

well.

And one thing that I want to cover now is the idea of pivoting and pivoting at table.

So the way I describe it pivoting is kind of 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 unpinning.

Now it sounds pretty simple pretty straightforward on the surface but it's actually a little bit tricky

to get a hang of at least it was for me.

So I'm a visual learner and what I'm about to show you is what finally caused it to click for me.

So imagine this simple two column table you've got years in the first column and you got a numerical

field like unit sales in the second column.

If we were to select the year column and pivot this table we would essentially take the year in each

row and turn it into its own column.

So now instead of a row for 1994 we have a column for 1994.

Same goes for 95 96 97 98.

So we basically flipped that table from a vertical format up to 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

years in the first row we could pivot to transform it into that vertical form.

In other words we take those years 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 corner pivoting is like rotating it up from vertical to horizontal and and pivoting is like rotating

down is like rotating it down from horizontal vertical.

Now one thing to note there's another tool in the query they're 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 or we don't have duplicate years

or unit sales pivoting and transposing would yield the exact same result.

But if instead we had two rows of 1994 for instance pivoting would collapse both of those rows into

a single 1994 column while transposing would preserve both versions and keep two separate columns each

with a 1994 header.

So one way to think about it is that transpose is kind of clunkier it's kind of brute force it just

takes the entire table and it just flips it on its side.

So I actually have a little demo file that I'm going to show you because you do have to really see this

happening in real time to understand what's going on.

So let me show you what that looks like.

All right so this file isn't available as part of the Course resources.

So just sit back and follow along for this one going to go ahead and grab my data as the CXXVI and I've

called this file on Pivot demo and I'm going to go ahead and open this up right here in the query editor

and right off the bat you can tell this table is a little funky.

There are some things wrong with this table.

Number one I don't have column headers which is not that surprising the column headers here are actually

years so in power be I took a look at the sample of this table.

It said OK I've got numerical values in each column.

You know I've no way of knowing if that's a year or if it's a similar value to the one in row two or

three.

It's an easy fix.

Just go ahead and click you first row setters and there you go.

Now our years have been promoted to hitters.

So we've made one improvement but this table is still far from ideal.

And what we have here is information about unit sales and revenue.

Got two different metrics broken down by year 94 95 each as columns and 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 create a lot of headaches.

Really what we're looking for here is a rectangular table with each dimension or metric as a column

and each observation as a row.

So ideally what I want to transform this table into is a format that has three columns year sales and

revenue that would give me a format that I could take and plug into my model and analyze in any way

that I choose.

So let's see if we can do that with our pivoting and on pivoting tools now first things first I don't

like the fact that I have years in columns so I want to turn these columns into rows and to do that

I'm rotating down from horizontal to vertical or on pivoting so that I can do is go ahead and select

all of my year columns go into transform and then press this and pivot columns button and there you

go.

It's transformed my year columns into your rows.

It's labeled that column attribute.

OK we'll fix that later.

And that hasn't totally solved my problem.

I still have unit sales and total revenue as my rows instead of columns but it's gotten me one step

closer.

And one thing to note before I move to the next step is that there's another way I could have done that.

And let me just remove that step and instead of selecting all five of these year columns I could have

selected this first column and chosen the pivot other columns option.

So you arrive at the exact same place just another way to get there.

So I've gotten a little bit closer got values here I've got years in a column.

The only adjustment I need to make now is turn these unit sales and total revenue labels from rows into

columns.

So I need to do the reverse of what I just did and take this vertical orientation and pivot it up into

a horizontal.

So you guessed it I'm going to select that first column pivot the column and this says OK where do your

values live.

Do they live in the attribute column or the value column.

In this case the value call in press OK.

And there you have it.

So now we've got first column which is attribute for a year and go ahead and name that call column year.

We've got a unit sales column and a total revenue column.

And this is a table format that I can work with as an analyst.

So that was a great demo of how both pivot and unpinned it can be used to kind of wrestle a messy table

format into something more workable.

Now last quick demo here if I take this all the way back to the start just delete each of these steps

back to square one because I'm not dealing with any duplicate values here.

I could use transpose.

In this case as well and in fact that's actually a more efficient way to do this.

I could select this entire table as is and essentially flip it or rotate it onto its side using that

transpose option.

So in the transform menu I can just press transpose here and that pretty much did the trick it put years

on a column put unit sales on a column and put revenue on the third column.

The only thing we have to do now is use those first rows and promote Henares.

And there you go.

We've gotten to our same ending point using both combination of pivot pivot and transpose.

So again kind of tricky to work with at first but these tools can be really really helpful when you

need them.

So with that we're going to go ahead and delete that query that I just created.

There you go.

Close out of the editor and there you have it pivoting and I'm pivoting.