(upbeat electronic music)
- [Instructor] We have all likely seen
conditional formulas in Excel.
A conditional formula in power query
can be thought of in three parts.
The first part is the "if" statement,
that says if the first condition is true,
then we return its corresponding result,
and otherwise we move to the next condition in the formula.
There's only one if statement in the formula.
The second part is the "else if" statement
that says if the second condition is true,
then we return its corresponding results.
There can be any number
of "else if" statements in the formula.
The third and final part
is the "else" result that the formula returns
if none of the previous conditions are true.
There's only one else statement in the formula.
Also, remember that if we set a condition
equal to a text value,
this value must be in quotation marks.
We are going to create a conditional formula
to determine what decade we need to use
to measure the inflation.
What we're hoping to do,
is be able to group the years into decades
to look at the inflation as a whole.
In this case, I want to get the last digit
that is in the year.
We first need to make sure that this is a text value.
Go ahead and change the data type and year to text.
Let's go ahead and create a custom column.
And we're going to say "Decade".
I'm going to set this up as text,
and pointing to the year,
and one.
They're turning the last digit on the end of the string.
See that this goes ahead and returns what we see here
in the last digit in the year.
However, let's go back into the formula,
and click on the formula here to get back into it.
And I'm going to set this up as an if statement.
So I'm going to say,
if the last character in this is equal to zero,
then I'm going to go ahead and return the year,
else I'm going to return null.
So go ahead and click okay.
We see here, that we have returned the decade,
if it is the first year of a decade,
it returns that decade,
otherwise it doesn't return anything.
Go ahead and set this equal to a whole number.
And there we have it.
We've used a conditional formula to create another column.
One more thing to note
is that there is a conditional column pop-up window.
However, one important thing to note
is that if we could say year equals,
enter a value,
we can select a column.
One issue we see, is that it is very difficult
to go ahead and to set it up equal to another formula.
Therefore, going down the custom column route
is often the best route to go down.
So what I want to do here is,
I'm interested in creating a calculated column
that measures the rate of inflation
from 1975, to 1985.
So in order to create a calculated formula,
I need to take this inflation rate,
just the percentage,
and divide it by a hundred
to get the rate.
Then I'm going to take the rate
for each of these time periods,
between 1975 and 1985,
and add one to it,
to get the factor of the inflation.
And then I'm going to multiply each of those years together
to get the inflation factor over a ten-year period.
So let's go ahead and do that.
Go into "add column",
create a custom column, as we saw before,
so I'm going to call it "Inflation Factor".
And I'm going to start typing in the formula.
Now it's going to be one, as I mentioned earlier,
plus the first year that we're going to do this,
is I'm interested in 1975,
divide it by 100,
and then I'm going to multiply this by the next year.
We can do it that way,
or we could just kind of take a shortcut.
So here, we're just going to go ahead
and copy this last piece of the formula
and paste it to cover all the years.
75.
Seven.
Eight.
There we have it.
We've multiplied one plus the inflation factor
for every year between 1975 and up to 1985.
Well, end of 1984.
Last, I'm going to go ahead and click "OK".
We see here that the inflation factor
is a new column in this table.
We can also see that some of them
do not have any values in them.
And this is because some of them
may not necessarily be populated those years.
But this is just an example
of how to do a calculated formula.
Lastly, we're just going to go ahead
and change this to a decimal number.
And we're also going to go ahead
and delete all the columns,
except this country column here,
This name, currently named "geo".
Go ahead and type it in as "Country".
Then click on the country name,
scroll all the way over.
Control.
Click on the inflation rate.
Right click,
remove other columns,
which is below remove columns.
There we have it.
We have a new column containing calculated formulas.
(upbeat electronic music)