All language subtitles for 004 The Power Query Editor_en

Instructor: When it comes to connecting to

and transforming data,

the query editor is our cockpit.

Now, in Power BI, from the Home tab,

we've already seen that you can launch the Query Editor

and that there's a lot going on here.

We're gonna be spending most of our time in this section

right here in the editor itself.

But let's take a minute and break down

the components of this interface.

Right up there at the top,

we've got our query editing tools.

This is where we'll find a lot of the tools

that we'll use to shape and transform our data.

Right beneath that, you'll see this formula bar

and it's got some unfamiliar code in there.

This is called M Code, and I didn't actually write it.

This is code that's automatically generated

as you apply different procedures

or transformation steps to your table.

For those of you who are advanced Excel users,

this is exactly what VBA does

as it automatically generates Visual Basic code

while you record a macro.

Now, don't get nervous

because you don't need to even know M Code

to use the query editor.

In fact, we're only gonna touch it a little bit

in a lecture or two.

But just know that you can write custom M code from scratch

or you can edit the M code that's automatically

been generated for you,

but you don't need it in order to use all of the tools

that we're going to cover in this section.

All right, so on the left side of the query editor,

we've got our queries pane that lists out

all of our queries.

Here you can see that I've got nine individual connections

or data queries in place, and you can click through

each query to see previews of your data.

You can edit the properties, change the applied steps,

basically any modifications that you wanna make

to an existing query, you can do so here.

As you select the individual queries,

you'll see this data preview

in the middle of the screen update.

This is where we can spot check and QAR data

and do things like change column types and update headers.

On the right side, we've got our properties pane,

and the most important thing to call out here

is the table name.

Now, it's really important to be strategic and clear

with your table names right off the bat.

One thing to keep in mind is that your table names

should be human readable, like territory space lookup.

Power BI doesn't really care about spaces in between words

and doesn't require the use of underscores or camel case.

So there's no need to create your table and column names

like you would in a SQL or a relational database.

Point here, just try and be strategic

with your table names from the get-go.

And then last, but certainly not least,

the applied steps pane here in the low array.

This is really powerful because what's happening here

is that every time you make a change to your data,

every time you apply some sort of shaping

or transformation operator,

Power BI will record an applied step using M code

and add it to the list here.

And what that means is that every time

this connection is refreshed,

Power BI will run through these same set of applied steps

to shape and transform your data.

And this is a fantastic way to automate data cleansing

and ETL processes instead of having to manually

run the same process over, and over, and over again

each time you connect to the data.

So the query editor really automates the process for you

and you can see each transformation as an applied step.

Perhaps one of the most meaningful things here

is that none of this is actually set in stone.

You can click on the X

next to any of these individual steps to remove it.

You can click through each of your applied steps

to actually visually see the impact

that it's having on your table,

or you can click the gear icon to the right

and modify the individual steps within this process.

So moving on to the Query Editor tools

I wanna just take another minute here

and introduce the three primary tabs or categories.

First, we've got our Home tab.

This includes some general settings, data source settings,

parameters, table properties, et cetera,

as well as some of the most common table transformations.

The next tab, the Transform tab

includes additional tools

that allows you to modify your existing columns.

You can split our group columns,

you can transpose your table, extract text, and much more.

And then finally, the Add column tools

actually create brand new columns within your table

and you can define those new columns

based on things like conditional rules,

text operations calculations, data operators, et cetera.

All right, so up next

we're gonna actually get our hands dirty

with some basic table transformations.