- [Instructor] Now, once you've created a connection
to your data source, you can open up
what's called the Query Editor,
and it looks something like this.
You can access this query editor window
in a few different ways.
One is to create a new query and choose the Edit option,
and another is to launch the workbook queries pane
from the data tab, so that you can right-click
on an existing query and edit from there.
So there are a few components to this window
that I'd like to break down.
Up at the top you've got your query editing tools.
These live in a number of tabs.
You've got Home, Transform, and Add Column.
We're going to break each of those down in the next slide,
but basically this is where all of those
data transformation and shaping tools live.
Beneath that, you have a formula bar
with some code written in there.
Note that I didn't write this code.
This is what's called the M language,
and it's automatically generated
as you make changes to your table,
so it's the script that's kind of powering the steps.
Think of it just like when you record a macro in Excel.
There's VBA code being written behind the scenes
that you may never need to touch or even see,
so similar case here.
M coding is outside of the scope of this course.
We're not going to touch it, but if you do want to get better
at Power Query and you want to do some more advanced
custom stuff, there's a great book
called The M Is For (Data) Monkey,
and I'd highly recommend checking that one out.
Now, beneath the formula bar, we've got our data preview,
which obviously just shows what our data looks like,
and the impact of the transformations
and changes that we're making, and then to the right,
you've got properties and applied steps for properties.
It's extremely important to name your table early
and to do it very clearly, because it can be a big pain
to change your table name after the fact,
especially if you've already referenced that table
in calculated columns and measures and things like that,
so give your table a clear and concise
and easily interpretable name.
And then finally, we've talked about applied steps.
This is where each of those adjustments
or transformations get saved and applied,
and note that there's a little X
to the left of each step if you want to remove it,
and a gear to the right if you want to edit it.
Now, drilling into each of these three key tabs,
Home, Transform, and Add Column,
let's see what we've got to work with.
Within the Home tab, this is going to include
our general settings, and some
of the most common table transformation tools,
so things like removing columns,
removing rows, sorting or reordering things,
changing data types, that kind of stuff.
The Transform tab includes tools
that allow you to modify existing columns,
so it overwrites the data in an existing column,
so you can split and group, you can transpose,
extract text, use statistical functions, and so on,
and then third, we've got our Add Column menu,
which actually allows you to create brand new columns
based on things like conditional rules,
text operations, calculations, statistical functions,
date logic, et cetera.
So in this section, we'll be diving into each of these tabs
and each of these types of tools.