Original subtitles

Welcome to this section on Microsoft Power BI Query Editor.

So far, every time we've loaded our data, we've just clicked on the load button.

But you may have noticed next to it is a transformed data option.

Now what we've got with Query editor is the ability to be able to transform your data.

You're going to see that this is a really powerful product and we're just going to show you a little

bit of an overview of how it works.

So we come back into Power BI and I've actually created a new Power BI.

We're going to load a new data file into this just to be able to show some of the examples with the

with the query editor.

So this is going to be a CSV file.

So we're going to do things a little differently.

We're going to go to get data.

And if we go down a little bit, you'll see on the most common data sources, we do have text CSV as

an option, so I'm going to select that.

Okay, then I'd like you to navigate to where you've downloaded the course files, and you'll see that

there's a file called employee Data dot CSV.

So please select that file and you're going to click on the open button.

Now you're going to see that things are a little bit different to when we've opened Excel.

It's going to show us a preview of the table because with the CSV or a text file, you can only have

one table of data.

You can't have multiple tables.

So in this case, it shows us straight away a preview of the data.

You'll see at the top that it gives you some options on just what it is, looking at, what it is using

to actually open the CSV file.

So it tells you what it's using as a delimiter.

And it's also telling you it's doing a preview of the first 200 rows.

Now, in this case, traditionally what we've done is we've used the load button.

However, we're going to move across and we're actually going to use transform data and this is going

to open up our power query for us to use.

So the power query has now opened, and what you're going to see is it's broken up into a number of

different areas.

So I'm first going to cover those areas.

We're going to get a bit of an overview, first of power query and how it works.

If we start on the left hand side, you will see that the list of queries are always shown on the left

hand side.

Now at the moment we've only got one query, but you'll find quite often when you start working with

Microsoft Power by quite a bit, you can often get multiple queries pretty quick.

But as I said at the moment we've just got one and the middle part, we've actually got a preview window.

So this is really nice.

It gives us a preview of our data and allows us to see the type of data that we're working with.

More importantly, you're going to see that as we start making changes to our data.

It actually shows us what happens to that data and what the effects are.

On the right hand side, we have something called properties.

This is actually really important.

This is going to be your table name that is going to be used in Power BI.

So what I'm going to do here is I'm actually going to change this.

And instead of employee plus data, we're just going to call this employee and essentially make it master

because this is quite a bit of employee information that we're going to be working with.

Now, below that, we have something called Applied Steps.

So applied Steps actually works something like a macro.

And what happens is that actually steps through these steps one by one starts to the first step and

then sequentially moves through each step.

So this is really nice because it shows you the logical progression that it goes through in terms of

the steps that you're working with.

So when you come back into this and you want to make changes, maybe a bit of time after you've done

your work, it's really nice that you can come in here, just work through it in a logical manner.

We're going to visit this a little bit more as we go through it and we get more steps that are going

to be applied.

Also, if you look at the top, you'll see that there's a menu option.

So just like traditional Microsoft options, you've got your menu and then you've got your ribbon below

this with quite a lot of options.

And you're going to see at first that the number of options can be quite bewildering.

But as you start working with this, you're going to quickly see that it's actually quite easy to work

with.

So let's get an understanding of how we actually transform data using the power query.

So at the moment, what you can see is that I've got the first column actually selected and this is

telling me it's an employee idea, so it's got a name and then also it's got a data type.

So this data type is really important.

If I click on the one, two, three, you'll see that I get a list of the different types of data that

I can actually apply this to.

So different data types that I could use.

So if I wanted to, I could say this is a decimal number at the moment is set to a whole number.

Or I could say this is text.

So let's say, for example, I wanted to say that this was text could click on that and then it will

ask me, do I want to change column type?

Now, at the moment, because I'm doing a change type, what happens is that Microsoft actually looks

at your data when you import it and it actually makes changes to the columns based on what it sees as

a type of data.

So here's asking me, do I want to replace the current step or do I want to add a new step for this?

So we're just going to say we're going to replace the current step.

And you can see now that the text has moved to the left hand side.

And I get an ABC which is telling me that this is a text type.

But you'll also see is that you do get filtering options.

So there's quite a lot of powerful filtering options that you can use with Power BI Query editor and

you'll see the traditional ones.

You can pick things bit by bit or you could use your text filters, for example, where you can find

things.

Again, something ends with something, contain something.

If you're working with numeric data, you'll see that you can use your number.

Filters find things that are greater than less than.

So they're pretty powerful.

Also, if you're working with the dates, if we go along here, you'll see that you've got a birth date.

If you go to your filter, you'll see that you've got your date filters equals before, after between.

But also it's got some relative dates as well.

So you can find things that are in the next period or the previous period and things like that as well.

So we do have quite a bit of powerful filtering options that you can apply.

Okay, let's move across.

And we got this national ID number.

Now, let's say, for example, I actually do not want this field to be in my analysis.

So what I could do is I can actually right click on this and you'll see that I get an option called

remove.

So when I click on that, it actually now removes the column from my actual table.

You'll also see that a new step has been applied and it tells me that it's removed the columns.

So that's basically the step that's been done.

If I look at the next two fields contact ID and log in ID, I might decide I actually don't want either

of those.

So I can select more than one by using the control key.

Right click on this.

And in this case, now I'd be saying that I'm removing both columns.

Now this one, we've got our manager ID, so that's fine.

I'm going to leave it.

I can see it's a whole number from the one, two, three.

But if we move on to our next option, which says title now I might want to look at this and say, well,

actually, you know, this is actually more of a job position.

So what I could do is I could right click on it and you'll see there's an option to rename it.

Or I could actually just double click on this and then it will allow me to rename it.

So we're just going to call this job position.

And as you can see, I'll just double click in the heading and I can then type in job position and it's

now renamed that field.

And you can see again, I've got a new applied step, so each time I'm adding an applied step, you

can see that it's now just adding that on and it's creating a sequence that we can work on.

Now when we move across to the next one is our birth date.

So when it comes to looking at our data types, data particularly important.

So what I'm going to do is I'm actually going to convert this one back and we're going to just say that

this is text.

So this is traditionally what would have come in on the CSV and Microsoft would have looked at this,

and then it applied automatically that it was a data data type and it converted it.

So let's have a look and see how we would do this if we didn't convert it.

Now, in this case, we're actually using British type of dates, so we've got the date, the month

and the year.

Now when I click on the data types, you'll see an option called using locale.

Now, when it comes to data, often it's a really good idea to use locale because if you've got day,

month, year, then it's going to be different to a month, day, year.

So those are different locales we can use.

So I'm going to say using the call, we're going to convert this.

And in this case, we want to convert it to a date and we're going to use the locale is going to be

the English United Kingdom.

But you'll see that on this list, there's actually quite a large range of different types of dates.

Now, obviously, the one that also gets used is English United States.

So if you did have month, day, year, then you would actually say that you're using that locale.

And in this case, it is the English United Kingdom, and it shows that it's got some examples here

of what it would convert to say on this.

And you can see that it's now successfully converted that to using the UK locale.

Can we get a move across now?

What we've got in this column is the marital statuses.

And you can see I've got m and se, so that could stand for married and single, for example.

And what I might want to do in my data analysis is actually have the words married and single actually

in my data analysis.

So let's say we're going to do that.

So what I'm going to do is I'm going to select this and right click and we'll see that there's an option

called place values.

So this is really nice.

What this allows me to do is to say that I'm going to find the hm.

Now, please note this is case sensitive, so we've got to say we're finding a capital M We're going

to replace this with married.

And I'm going to click okay.

Now you can see on the step, the applied step, now that all the marrieds, all the names have been

changed to married.

I'm going to continue with this.

We're going to replace values.

And what I want to do is with the SE, I'm going to actually purposely misspell this.

So I'm going to say instead of single, I'm going to say single.

So we're going to put that in there.

And you can see now it's changed those.

I know it's incorrect, but we're going to correct it just now.

We're going to move across to gender because gender, we actually do the same thing.

We're going to say replace values for that.

And in this case, now we're going to use M for male.

And we use female.

So again, just right click on that and we're going to say now in this case that we're going to replace

the values.

I'm going to say a female.

Okay, so now we've got four replaced values.

And one thing I want to show you is that if you go back in this list and say, I click on the first

replace values, do you see how my preview window now changes to show me what it looks like at this

point in time?

So it's not now got the next three replaced values being shown.

So please note if you do click back, you'll see now how the preview window will change according to

where I've selected.

The next point that I want to make is you'll see that this little gear icons and what this allows me

to do is actually to be able to change and to be able to replace options.

So I now made a mistake with this second one.

So I'm going to click on the gear icon and you can see that I've got SE and instead of shingle, I should

have said single.

I can now come into this and I'll just click okay.

And you'll see now that it's correctly, now updated that.

Now, when I jump back to this step, do you see how the male and female has now been put into the preview

window?

Another thing that you can do is if you right click on this is you can actually rename the step.

So what I could do is I could say SX to single.

So if I come back to this in a couple of weeks time and I looked at the set of Applied steps, then

I would know what those would actually mean.

So if I went through each one of these, I could actually rename it and I could listed also you will

see that you can move items so you can move before and after If you wanted to move the step.

Also, you do not have an undo facility here.

What you do is you actually click on the delete icon and when you click on the delete icon, it will

remove that step.

Okay.

So at this point, what we're going to do is we're going to conclude this lesson.

I'm going to continue with this in the next lesson.

I will see you there.