Original subtitles

Now when it comes to connecting and transforming data the query editor is our cockpit and power be I

from the Home tab.

You can click edit queries to launch the query editor and there's a lot going on here.

We're going to be spending most of our time in this section right here in the editor itself.

But let's take a minute to break down the components of this interface right up there at the top.

We've got our query editing tool box.

This is where we'll find all of the tools that we'll use to shape and transform our data.

Right beneath that you'll see a formula bar.

And it's got some unfamiliar code in there.

This is called M code and I didn't write it.

This is code that's automatically generated as you apply different procedures or 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 and code to use the query editor.

In fact we really won't be touching em code at all in this course.

You can write custom code from scratch and you can edit the code that's automatically generated for

you but you don't need to in order to use all of the tools that we're going to cover in this course.

Now on the left side of the query Ed we've got our queries or query list here you can see that I've

got nine individual connections or data queries in place.

You can click through to see previews of your data.

You can edit the properties change the applied steps.

Basically any modification that you want to make to an existing query you can do so here.

And as you select individual queries you'll see this data preview in the middle of the screen update.

This is where we can spot check and queue our data and do things like change column types and headers

on the right side.

We've got our properties pane most important thing we 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 because

it can be a pretty big headache to change them later on especially if you've already referenced them

in multiple calculated fields and measures.

So rule of thumb.

Be strategic and careful about your table names from step 1.

And then last but certainly not least the applied steps.

Pain here in the lower right.

This is really 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 be I will record

an applied step using that M code and add it to the list here.

And what that means is that every time this connection is refreshed power b I will run through that

same set of applied steps to shape and transform your data.

And that's a great way to automate things like data cleansing or ETF processes.

You don't want to end up running the same processes manually over and over and over every time you connect

to the data.

This automates that process for you.

You record it once and then you're good to go moving forward.

But perhaps the most helpful thing is that none of this is set in stone.

You click on the X next to any individual step 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 on the right to modify or edit individual steps in the

process.

Now moving on into the query editing tools want to take a minute to 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 etc. as well as

some of the most common table transformation tools the next tab transform includes additional tools

that allow you to modify your existing columns.

You can split or group columns.

You can transpose your table extract text et cetera.

And then finally the add column tools actually create brand new columns in your table and you can define

those new columns based on things like conditional rules or text operations calculations date operators

etc. From here on out we're going to jump into demo mode actually get our hands dirty and practice working

with these tools.

Got a lot to cover.

So let's dive in.