- [Voiceover] The Query Editor in Power BI Desktop
is incredibly powerful and will give you tools
that allow you to transform data in a number of ways.
What we're going to use as our data set for this
is some historical data provided as a free download
from the US Census Bureau, which has information
about the population in different states, historically.
Let's go to Get Data, this data is stored in Excel
in the Chapter 03 files, Transforming
is the name of the workbook.
And it looks like there are four things in it,
four types of objects that we can
navigate to, and there are.
Two of them are spreadsheets, two of them are
named ranges, that is, tables.
Table 1 and Summary By State.
So if we looked, for example, at US Region and State Pop,
this is a spreadsheet, and across the top,
we have our column names, State, District, Region, so on.
If I choose the Summary By State, very very similar.
What's the biggest difference?
Not much.
There's a bigger difference with this US Population table,
because with the US Pop table, for whatever reason,
when the Query Editor looks at this data
in the spreadsheet, it doesn't automatically promote
the headings, Name, Admitted and
the year numbers as column headers.
and the reason it doesn't is because 1790, 1800, 1810
look like data, look like numbers, not like labels
even though they're the year numbers.
However, in the table, that work has already been done.
Somebody selected this data and said my first row
contains my headings, my descriptive information.
So a couple of different choices.
If we choose the table, it saves us a step
because we no longer have to promote our headings,
so we'll go ahead and do that.
Load it in.
Here's our data.
And what we have is the name of a state
in the first column with one exception here,
the District of Columbia, not a state,
and another exception down at the bottom,
the United States, this is a total or a summary row,
not a state, so 50 rows of state data,
the District plus the national summary.
And decades for the census, number of people,
the population of that state.
In some cases it was even a pre-state.
You'll notice we have two times taken a census in Alabama
before it was actually admitted to the Union.
And then we have a key field, a unique number
for each of these items here.
This is a great data set, but it's not in the shape
I'd like it to be in to be able to work with it.
For example, I don't want to have the District of Columbia
included and I don't want to have
the United States included, but you'll notice
that they have something in common.
So let's drop this into the Query Editor and go to work.
On the Home tab, simply choosing Edit Queries
pitches us in here, I can fill the screen with this.
And you'll notice we have some null values there
represented as empty when we took a look at our data,
but there really is the null value,
which is different than a zero, right.
A zero here would mean that someone went up to Alaska
in 1800, counted and found no one, which of course
was not the case.
There were people here historically before
1790, 1800 and so on.
Whereas null means we didn't count at all.
So that's the difference between putting in a zero value,
sometimes it doesn't make any difference at all,
but sometimes it really does provide clarification
to say it's a non-zero value, it's a non-numeric value.
Let's take care first of dumping the District of Columbia
and dumping the United States down at the bottom.
It's easy enough to do, all I need to do is say
if, when we take a look at the year they were admitted,
there isn't one...
Then they should go away, and at this point
I should have 50 rows left, exactly.
From Alabama to Wyoming, we're all here,
and we have filtered out those two rows.
Notice it appears here in the Applied Steps List.
If I want to undo that, they're back.
If I want to do it again,
easy enough to do.
This filtered row step now is part of my query,
and therefore part of my model.
It's also possible that if a state has no data, if we
never collected information in Alaska,
in Arizona, in Hawaii, in Idaho,
that I don't want them in this list at all.
The easiest way to do that is to go out to 1860
and say if there wasn't a value in 1860,
then please remove them from the list.
And in this case, we now have 42 states left.
Eight of them are gone, all of those states,
where we never held a census before 1870.
Notice also that some of our data for example,
these values in 1790 are text values.
1800, 1810, these aren't, but 1790 is
and my clue is that they're left-aligned.
I could check to see if any of the others were
or I could simply hold shift, click on the
first and last column and grab them all,
because what I'd like to do is make sure that all of these
have the same data type.
And the data type I would like to have here
is whole numbers, we don't need a decimal point,
we're counting people.
And notice when I do that now, all of my numbers
go to the right and all of this data is being treated
as numbers, rather than having some of it treated as text.
If I'd like to get rid of the null values,
even though I know that I'm not going to see them
in my results set, I can do that,
and to do that, select the area, in this case,
all of these columns where we'd
like to replace the null values.
Click Replace Values, and it says what's the value to find,
and I'm going to say Null, and replace it with,
now I can't really replace it with nothing,
but I can replace it with a zero.
And notice that that worked.
Again, there's a difference between no people
being there and we didn't go at all,
so if we say, ah, not so much, I actually want to
roll that step back, simply delete it
from our list of Applied Steps.
Note that you can sort and directly filter
using any of these filter buttons at the top
of our columns.
Including choices like Equals, Does Not Equal,
Greater Than, Greater Than or Equal To
and Between, those same types of number filters
that we have available to us in Excel.
If I decide I'd like to do something different
and remove one of the steps, I can do that
even if it's not the last step.
So this isn't quite like Undo in Word or in PowerPoint.
If I wanted to unfilter the rows,
I can simply remove that step.
If I do, and other steps relied on it,
it could break the query, but if I know
that filtering was different than anything else
that I've done, I would be okay with deleting
this step and leaving Changing the Type
and Sorting the Rows still in place.
Each of these steps is essentially
an individual transformation.
So far we've been using the tools in the Transform Group
but there's an entire Transform tab on the Query Editor
that allows us to do things like Count Rows,
Use First Rows as Headers, some of these items
that we see, in the group on the Home tab,
like Data Type and Rename, but we also have
the ability to do something called Fill
where we can take a value and fill it down a column
or up a column, the type of thing that we would do
using the Fill Handle in Excel.
When would you use this?
As the same time you would in Excel
if you have a calculation that you've entered
in a column, which we'll do in the next movie
and you want to be able to fill it down or up.
Among the tools that are in the Any Column group,
is a powerful tool that allows you to unpivot columns.
Let me explain what we would want to do with that.
Sometimes what I want are rows and rows and rows of data
and what I have here is essentially a pivot table.
On the left I have the state names and going across the top
I have the decades, 1790, 1800, and so on.
What if I wanted to have an individual row that said
Alabama 1790 and a number.
Alabama 1800, 1,250, Alabama 1810, 9,046,
and I might want that kind of a data set because
that's exactly what I need to be able
to illustrate something.
So, I can unpivot a column.
I can unpivot all of the columns,
and I would probably want to do that with
all of the columns rather than just with some of them.
This is a pretty startling thing the first time you do it.
I'm going to unpivot, you can either unpivot
the columns you've selected or the columns
that you haven't selected.
So if I choose to unpivot the columns,
what I have, is I have, there's my records for Alabama,
one for each of the decades, the year it was admitted
didn't change, its key field didn't change.
And then it says Attribute and Value.
Well, the Attribute here is this is the year,
so I need to name this.
Double click and this is the population...
In that year.
So I've taken this nifty little table that had 50 elements
in it and now it is much longer.
It would be approximately 50 times each of the decades
that we had data for.
Notice that in years where there wasn't data,
there is no record being produced.
This is pretty slick.
Now, what if I change my mind?
What I can do is I can actually unpivot the columns,
I don't need to rename them anymore, they won't exist,
and I can unpivot and we're back to where we were before.
If I have data that's rows and rows and rows,
and I want to create a pivot table from that data
to be able to create a report or visualization,
this is how we pivot.
We also have the ability to move a column
to the beginning or the end, to the left or to the right.
Now I can also do that with drag and drop,
and it bothers me that this key columns is
all the way to the right, I can actually drag it
up here to the start, and there it is.
But I also had the choice to select this column
and rather than drag it, simply say
throw this one to the beginning.
I can adjust the widths of any columns,
just as if I were in Excel.
A couple of other transformations
you may want to know about,
if you have data and you want to rearrange it,
this would be for example when you have a table
like this and the dates are down the
left-hand side, and the states are across the top
and you're like, wow that's huge,
50 columns of states, you can transpose your data.
You can reverse rows so that these rows go to the end
and that's not necessarily about sorting,
because it may be that the data isn't sorted
in any particular order, you just want to reverse
the order that is there.
You can extract some characters out of a string.
So, you could say that I'd like to, for example,
see the first two letters of a state.
Or if I had phone numbers that I wanted to
be able to take the first five characters to get
the area code in its parentheses.
I also have the ability in number columns
to do statistics.
Those are our aggregate values
that we would expect to see.
Some standard math, Scientific, Trig,
Rounding, very useful all the time
you can round up, round down, or simply round.
I'd like to go grab one more set of data now
and do a little bit more transformation.
So we're in the Query Editor, we need to close
and apply our changes.
And it's not quite done yet.
So now we only have the states
where there was a census done in 1860.
I'd like to get another piece of data,
also in Excel, also in the same Transforming Workbook.
And let's now pull in the US Region
and State Population table.
And again, we can either choose the spreadsheet
or we can choose the table, doesn't matter.
Going to load the table.
And it's okay that both of these things are in
the same model, that's not a problem at all.
And I want to edit this query,
which is the Summary By State, District and Region.
Remember that if you want to see the column headings,
you can just open these up.
What we have here is data arranged by
districts and regions, so New England
and Mid Atlantic are two districts in the Northeast region.
So we have extra data that we don't necessarily want,
and this is not that unusual that the data
that we start with is a report,
is output from some other system and has
subtotals and totals embedded
that we don't want to work with.
It's worth knowing how to do this
because you'll need to do it a lot.
Now remember, we could just go in and try
to edit this but we don't want to do that.
We want to edit our query.
So drop into Edit Queries.
There are two queries in here,
make sure you have the right one.
And the easiest thing that we can do
is we can look and say well, if there wasn't a value
for that state in 2010, it's probably not a state,
it's probably a region, and that's actually true
all the way down here, including US Total and DC
and some place here we'll have Washington DC
perhaps as an independent set of data,
and that's here so we might have to get rid of it
separately if we wish to, because we held a census there.
But let's first go do our first piece of transformation
and say if it's null for the 2010 population,
get rid of it.
And all we have left then is a decision to make
about the District of Columbia.
If you scroll down, we have 51 areas here,
so we know who's left over.
And if we decide that we don't want to have
the District of Columbia, the easiest way to do that
is to call it out right here, and to say
you know, not the District of Columbia either.
We've now done our filtering, if we apply and close this,
when we return, we'll have a data set that has
50 states in it, as soon as we refresh our data.
So here's our 50 rows.
And if we take a look at our other data set,
42 rows, say wow, well, we'd like to be able to
work with these two together down the line,
so maybe it would be better if I brought those
other missing eight states back and you could.
You could just go back in and edit your query
and get rid of the step where we filtered those states out.
And we can do that because the query still has access
to all of that information, we're just filtering it out
as part of our transformation.
In the next movies, I'll show you how to further edit
and refine your Data Model using the
Power BI Desktop Query Editor.
But for now, save this, if you'd like.
We'll be seeing it again later.