Original subtitles

Can this lesson.

We're going to continue to transform our data using the query editor.

So I'm going to move across a little bit.

And you'll remember we got to the gender.

We've got our list of applied steps that we've got at the moment.

If you look at our higher date, you'll see that that automatically converted.

But just remember, if you need to convert it manually, can click on this.

Just say using the call and you can change it.

Please note, though, that if it has changed to a date and it's incorrect, is that you need to change

it back to text again before you can do a transformation.

Otherwise you're going to be trying to transform data that's already been transformed.

So just be aware of that.

If it's if it's not correct, just transform it back to to text.

Now what you'll see is that as we go along here, we've got some other data that we've got celery flag,

vacation hours and so on, and then a whole bunch of columns that we probably don't need.

Now, one of the things that you can do is you've got this great option called Choose columns up on

your ribbon.

So if you go up to the top here, say choose columns, this will allow you to actually see a list of

all the columns.

I really like this.

It makes it so much easier to actually come into this.

So what I might do is actually everything that is off to sick leave hours is I'm actually going to remove

all of those columns from my actual query.

So there we go.

I've removed all those columns and we just now go up to the sick leave hours and we've now got the data

that we're working with.

So at this point now, we've done some transformations, we've made some changes.

I just do want to highlight that if we go to an option called the Advanced Editor, let me click on

this.

You're going to see that this produces script.

So everything that we're doing in the graphical interface at the moment is actually just creating scripts

in the background.

So it makes query editor extremely efficient.

So when query editor is actually working, what it does is it actually just runs a script and it doesn't

actually save any of the data that has been transformed.

It basically runs the script, transforms the data as it's working, and it actually only then imports

the data that has been transformed and changed right at the end.

So this can make it incredibly sort of efficient when you're trying to bring data into power by especially

if you use filters and you're removing data that you don't need, and then you only bring in the data

that you actually do need.

The next part is if you look at the script itself, you can see it's not too difficult to actually follow.

You can see that the commands that have been used so often, people may study the script itself and

go in and actually make changes.

Please note, though, that it does have to be exactly the right case sensitivity.

So you've got to follow exactly the rules that are there at the moment.

Otherwise you'll find that actually using the graphical interface just works perfectly and you can pretty

much do everything you need to.

Now, when you get to a point where you've done your list of transformations and you're happy with the

results and click on Done and you'll see that there's an option on the top here called Close and Apply.

So I'm going to choose close and apply it.

And what that's going to do is it's going to close the query editor and it's going to now apply this.

And you're going to see now that this data is going to be loaded into my Power BI desktop.

So we've got now our Power BI desktop.

If I go across to my fields, you'll see that I've got my employee master.

Remember I change the table name.

Also, I've only got the fields that are selected and you can see that the higher date and the birth

date are both correctly being seen as data fields.

And also I've got some numeric fields here that are correct as well.

So if I wanted to now create a report out of this, let's say I wanted a matrix and I wanted to see

my job position, how many people I've got by different gender, then I could take my job position,

drop that into my rows, I could take my employee ID and we'll do a count on that.

And we could take our gender and we could drop that into a columns.

And you'll notice that I get female male and it's now doing a count of all the options that we've got

of the number of people.

So just in terms of the values, what we would do is we would say that we want this to be counted.

And there we go.

I've got 84 females, 206 miles, 290 rows in total.

Okay, so we've created now a table and we're going to take this to our human resource department.

And they say, that's fantastic.

But one of the things that they really need to know is what is the age of the person?

And we can see here that we've actually got a birth date, but we don't actually have the age of the

person.

So let's say we're going to go back into now our query editor and we're going to transform this so that

we can actually get that as a new field.

So what we're going to do is we're going to go up to our ribbon and you'll see that there's an option

called transform data, and we're going to select that, and that's going to open our query editor.

So you can see our query editor is now opened and we're back into exactly where we were.

We've still got a list of applied steps that we've created so far.

But what we want to do now is we actually want to create a new field.

So we're going to go across to our birth date and we're going to use that as the basis for calculating

what is the age of a person.

Now at the top, you'll see that with the menu options, you get a number of different transformations

that you can do to an existing field.

Also, what you get is the ability to be able to add new columns based on existing fields.

Now, this is what I'm going to do, is I'm actually going to use the birth date to do a calculation

for the egg.

So I'm going to keep it on add column and we're going to go across to the date and you'll see that there's

a number of different options that I could do here.

For example, if I wanted to know what year the person was born in, I could actually go to the date,

go to year and just choose the year itself.

And what you'll see is now that the year will be taken out of the birth date, and we now have just

that as a piece of information.

So I could just say here, call this, say our birth.

Yeah.

And there we have a new field.

But let's go back.

Let's look at the tasks that we're trying to do, which is actually to calculate the eight.

So I'm going to select the birth date again, and we're going to go back to add column.

Just make sure add column is selected, go back to date and you'll see that there's an option called

A.

So we're going to select that.

And you can see now it creates a new column called Age.

We also get a different data type.

You're going to see that this is a duration data type.

So this is telling me that this is the duration.

Now, what it's done is actually not really what I wanted is calculated The number of days that the

person's been alive for.

It's also got you can see some time elements as well, which I don't really need.

So what we do need to do is actually some transformation to this field to actually get it a little bit

better.

So I'm going to select my age field and this time now I'm going to go to my transform options.

And you'll see with the age that now my duration under the date and time column is actually available.

And what I can tell this to do is actually convert this into rather a number of years rather than a

number of days.

So I'm going to say show me the total number of years.

And there we go.

It's done now, conversion again.

And it's showed me the number of years.

But as you can see, this is probably not quite what I want.

This is actually now showing my decimal.

Now, the reality is with somebody's age is they or that age until the day that they actually turn to

the new age.

So one of the transformations I could look at is I could say, take this field.

I could actually use a rounding.

So if I round this down, then it would actually round it down to the actual number of years that there

have been a lot.

So I'm going to say run down.

And you can see now that this actually does give me the age as a rounded down option.

So as you can see, there's a couple of steps involved.

But I wanted to show you this just as an example of one of the things that you can do with Query Editor.

You're going to find that it's a really powerful piece of software to help you with the transformation

of your data.

And there's a lot of different things that you can do.

If we go back now, if we close and apply this and let's bring that back into our power.

BI you'll now see that we do have a new field called age and we can now look at say, Oh, let's just

get a table.

Let's go to our job positions again and let's say we wanted to know what is the average age for each

of the job positions.

So let's just drop that in there.

Let's get our age.

Drop that in there.

Now, you can see by default it summed up the age, but in this case we would want to average age.

You can now see for that job position.

You've got your average age.

If you wanted to I'm just going to delete this, make this a bit wider.

We could add a couple more ages as well, and you could actually then say, what is the maximum age,

i.e. the highest age?

What is the minimum age?

So let's say, for example, we dropped the age again.

You said in this case you wanted to know what is the maximum is the max age.

Take the edge again.

Take the minimum age.

See the mean age.

Also, you could actually see how many people are in each of the positions at our employee ID.

Try that again.

Have we got to count on that?

And you can see how many people.

So as you can see using the query editor, we've created a new field that we didn't have before, but

it's allowed us to do some powerful data analysis and to get some new insights into our data.

We're going to conclude the lesson there.

I will see you in the next one.