[MUSIC PLAYING]
Welcome to Excel Magic Trick number 1546.
Hey, we've got to figure out how to get the previous row
value using Power Query.
Now in Excel, it is easy.
But Power Query, getting the previous row, let's just say,
it's not a relative cell reference.
It's actually going to involve using an M Code lookup.
So we'll get to see the formula for how to look things up
in M Code and how to get the previous row.
Now this concept is the most important thing in this video.
But the formula we're going to ultimately calculate
is change in stock price.
Now, the data set we have, we have date and GE close stock
price.
And I simply need to take whatever today's
prices minus the previous day.
Now in an Excel spreadsheet formula,
we simply do, hey, equals relative cell reference.
That will always look one to the left, minus.
Please always look as a relative cell reference
one to the left and one up.
Control enter to put the formula in the cell.
Double click and send the formula down.
When I go to the last cell and hit F2,
it's still the same pattern of relative cell references.
One cell to the left minus one cell to the left, please
look at the previous.
Now, in Power Query, it's just not this easy.
The looking up one cell in the same row
as the formula, that's not so hard.
It's looking up the previous item that is hard.
And here's what we're going to have to do.
In Power Query, M Code will have to give it the whole table.
Then we'll have to say what row number.
Then we'll have to say which column name.
So we're going to have to do a two-way look up formula.
Now, if we look up here, here's our formula.
That will be the name of the full table with rows
and columns, then positional index operator,
those are those red curly brackets.
Inside there, we'll have a look up operator
that looks up the index which we created
in the current row that gives us the previous row's position.
But the positional index operator
will evaluate to a row number that this table
uses as the first part of a two-way lookup.
Then the field axis operator, square brackets,
that will be the column name.
So in Power Query, this is how we do lookup, full table,
row number, column name.
Now, there's something else we're
going to have to do over in Power Query.
We're going to have to not only deal with this first row, which
doesn't get a calculation, but we're also
going to have to see how to use the if function.
So let's do it over here in Excel
first if there's a logical test.
Now over in Power Query, we'll use if.
But there's no equal sign.
It's lower case, and we don't use that parentheses.
We do have a logical test.
Now, over in Power Query, we'll add an index column
that increments numbers.
But in our formula, we need inside logical test,
a Formula element that will give me the number 1, 2, 3, and so
on.
The way we do that is with the rows function.
Now, I'm sitting in C2, so c $ 2 colon C2 close parentheses.
Rows counts how many rows there are
in a range, how many rows are from 2 to 2, 1.
But that has a dollar sign.
So as I copy down, it will remain 2.
But that one is free to move as a relative cell reference.
So it will go to 3, 4, 5, and so on.
That's an expandable range and rows
will give us, as we copy down, 1, 2, 3, and so on.
Now, I'm only interested in the first row.
So I say, hey, are you equal to 1?
That's our logical test.
Comma, if it's true, then what do I want?
I want to show nothing.
In a spreadsheet formula we use double quote double quote.
That is a 0 length text string that inside the formula
will show nothing.
Over in Power Query we're going to have to use a null comma
value if false.
One cell to my left minus.
Oh, so easy over here in Excel--
relative cell reference, one to the left.
And please go up to the previous row.
Close parentheses, control enter, double click and send it
down.
Down and F2.
One other thing about the if, we have our three arguments which
we'll have over in Power Query.
But we do not use commas.
We'll have to use then to get to value if true.
And then else, to get to value if false.
All right, now, let's go over to Power Query.
I've already imported the same table from a text file,
so it's just sitting over there in Power Query.
And if you don't have this pane, go up to data.
Get in transform and queries and connections,
that's Power Query.
I click that.
And now I can double click GE stock prices.
Hey, there's our date and our GE close.
Now I'm going to rename this query something
like change in stock prices.
Now, the first thing we need to do is add an index column.
So I go over to add column.
There's index.
We're going to add--
well, there's base 0.
Power Query is base 0 which means row number 1
is 0, row number 2 is 1, and so on.
From 1, that's like over in Excel.
But I want to do a custom.
We're going to start at minus 1.
Because remember, when I get to row 2, I need a 0.
Which is what we're going to use to get to the previous row.
In Power Query, the first row is base 0.
The increment will be 1.
Click OK.
Now we can see the table dot add index.
There are our numbers.
This index column will serve.
So if I'm down here trying to take 998 minus,
well, inside the 2A lookup formula,
I'll have to say row 9, which is the previous row.
Now, I'm going to come over here and hit F2 just
to make our formula neater.
If I get rid of spaces, then when we use this previous step
name, which is the entire table with rows and columns,
remember we have to have the full table then the row
and column.
So I'm going to use that name in our formula.
We need to add an extra column.
So I go up to custom column.
We're going to name this something like, change
in price.
Now, all I want to do before we do our
if is see if we can with our formula
look up the previous row value.
Now remember, we have to do two-way lookup.
So I'm going to put Control V. I copied that from over there.
That's the full table from the previous step.
Now, just to show you a couple things about Power Query.
I'm going to click OK here.
Remember, that's a full table.
When I click OK, two things are going to happen.
The first thing is, we're using a custom column,
so table dot add columns.
There's the previous step.
That's the name.
Each is the syntax we use to make a calculation in each row.
Technically, that's a substitute or a shorthand
for a custom function.
And there's our element.
That's add index.
That's the previous step.
But notice, now we have a column filled with tables.
If I click in any one of these cells, not on the word table
but off to the side, you can see the full table.
And it's the same exact table in every single row in our table.
Remember, for two-way lookup we need our table first.
Now, we can edit up here right in our table dot add column.
Or we can click over here.
For the time being, I'm just going to do a couple tests.
We first can see that we can have a table in every row.
Now, just for kicks, let's do our positional index operator.
And I'm going to say 4 just to randomly pick it.
That represents row 5 because we're doing base 0.
Now if I hit Enter, what will it do?
It will take the full table and look up this row right here.
When I hit Enter, now it returns a record
which is a particular row from a table.
If I select off to the side, every single row
has the same record that we can clearly see is from row 5.
So what we've illustrated is now we
know we can have a record in a column.
We also see the positional index operator.
Now, looking at this record, let's
say I wanted to look up GE close.
So now I could do field access operator.
All right, so I have square brackets
to look up that column.
Now we have our two-way lookup table row column.
When I hit Enter, now I get an error because I can't spell,
"clode."
Backspace s enter.
And there I've looked up from row 5 column GE closed $10.30.
Now we're going to use our lookup
operator because we were smart.
So backspace square bracket index close square bracket.
We started at minus 1.
So in any particular row, the lookup operator
can simply see from this column the given row.
So right here the formula, we'll see row 4,
which is the previous row.
So this row right here, we'll see 10.3.
But the next row, we'll see 9.96.
Now when I hit Enter, that's our two-way lookup formula
to get the previous value.
We can see right here, 9.9, that's the previous value.
Now just like for the Lookup operator right
before our two-way Lookup, now I'm
simply going to type the name of this column, Lookup operator.
Hopefully I spell it right this time, square bracket minus.
But wait a second, Power Query is case sensitive.
If I hit Enter, I get an error.
Backspace capital E and enter.
And that is beautiful.
That's the formula we use to get the change from the current row
and the previous row.
Now let's deal with that error.
We could edit up here.
But we can also come up to applied steps.
If you see a gear icon, you can click that to open.
And here's how we do the if.
Lower case if, a space, and we're
interested in the index column when it's equal to minus 1,
so right there.
And now that I'm in this dialog box, I can double click index.
When you are equal to minus 1, space, not a comma,
then please give me null, all in lower case.
And then else.
And there is our if with our calculation for change in stock
price and our two-way look up.
When I click OK, I'd better make sure everything spelled.
Well, nulls, that's not supposed to have an s,
so backspace and enter.
You could put a 0 there if you wanted to.
But I'm putting a null because we don't have any calculation
to make.
Now if we load this to the Excel spreadsheet,
that null will show up as an empty cell.
Now, we wouldn't do this complicated formula
if in fact the data was in an Excel spreadsheet
and we could use a regular formula.
This is just when we have to use it
in Power Query for some particular reason.
Now, I am going to try and load this to the sheet.
But guess what, I can't load it because it's already
been loaded.
So I click close and load.
Then over here, right click, load 2, select table, and 1,
click OK.
And so there we have our Power Query output
to the Excel spreadsheet, all right.
A lot easier to do in Excel, but if we
have to do it in Power Query, it's
awesome to know how to do a two-way Lookup to get
the previous row value.
Now, if you like that video, be sure to click that Thumbs Up,
leave a comment, and subscribe because there's always
lots more videos to come from excelisfun.
All right, we'll see you next video.
[MUSIC PLAYING]