All language subtitles for 005 Basic Table Transformations_en

Narrator: Jumping right in,

if you go to the Query Editor and look at the Home tab,

you'll see all sorts of basic table transformation options.

Let's talk through some of the most common

and frequently used ones.

First off, you can choose to keep

or remove certain columns or rows from a table,

and there are a few interesting options here.

Looking at the column options,

you can either select the columns that you'd like to remove

or select the columns that you'd like to keep.

Now, that might not seem

like a really important distinction,

but it can be very helpful in certain cases.

So let's say that you've got a data connection to a table

that might have a variable number of columns.

Let's say one month the table has 10,

and then the next month it has 20.

But at the end of the day,

you always want to keep the same set of five columns.

If you were to open up the Query Editor

and manually remove each of the columns that you don't want,

then any new columns that appear won't be accounted for

and will ultimately end up in your final table,

which is what you don't want.

On the other hand,

if you select the five columns that you always want and say,

"Hey, Power BI, these are the five columns that I need.

Anything else that you run into or find in that table,

just remove it."

All right, so taking this approach,

you'll be good to go moving forward,

regardless of the other columns

that are present within the table.

I know it's a bit of a nuance,

but it's actually a really helpful tool

when you run into these types of use cases.

Now, as far as rows are concerned

you've got some basic options here.

You can remove the top rows or bottom rows.

You can set a specific number of rows to keep or remove,

and this is really helpful if you have files

that might have header rows or footers

or totals that you need to strip out.

Other options here, you can remove alternate rows,

or you can remove duplicates.

And that could be a great way

to actually create a new lookup table from scratch

by creating a unique list of IDs.

You'll also find some basic sorting options,

like A to Z or low to high,

as well as options to change your data types

or promote your header rows.

Now, Power BI is actually pretty smart about headers,

and by default it should identify

and promote those header rows for you.

But in case it doesn't,

you can always do it manually using these tools here.

And then the last thing I want to call out,

if you right-click any column header,

you can access a lot of these same common tools.

Plus, you can do things like duplicate, move,

or rename columns.

And this is a common theme

that we'll see time and time again

as we work with the Query Editor,

which is that the same tools are often available

in multiple places, in multiple menus, multiple tabs,

sometimes even in multiple views within Power BI.

So with that, let's head back over

to our AdventureWorks Report,

and we're gonna connect to our product lookup data.

So like I've mentioned,

there are multiple ways to connect to data in Power BI.

And from within the Query Editor,

New Source is going to open the Get Data menu.

And we can click the bottom of it

to see some of the most common options here.

We have our Text and CSV option.

And if we click More, Power BI is gonna launch

that same Get Data interface, right?

And all we need to do is click Text/CSV and then Connect,

right, so multiple ways to accomplish the same thing.

So from here, we're gonna locate our product lookup,

right, so AdventureWorks Product Lookup.

And we'll open this.

Like we talked about, this process becomes very easy.

It's a very repeatable thing,

and you'll get very used to doing this over and over again.

So we're brought back in to our data preview window,

file origin, our comma delimiter,

the data type detection based on our first 200 rows.

Again, we're gonna dig into all of this

within the Query Editor, but, you know, overall this looks

like what I would expect to see from this kinda data set.

So we'll click OK,

and this is gonna add this into the Query Editor.

So now over here on the left-hand side,

we've got both of our queries, right?

We've got our Territory Lookup table,

and we see our preview here.

We have our couple of applied steps

that automatically happen.

And we've got our AdventureWorks Product Lookup table,

same thing.

Right, so we've got our query over here, our data preview.

We've got our applied steps, our file name.

And one of the things that I want to quickly look into is

what exactly is happening with these applied steps?

Right, we've got a Source, Promoted Header,

and Changed Type applied step.

So because these are all individual,

we can click into each one of these

and see exactly what's going on.

Right, and if I expand my formula bar,

and we've got this M code here that's saying,

"Hey, we've got this CSV document,

here's its location," right?

And this is gonna be unique to me, right?

So this is my file drive location,

and here's the delimiter,

the number of columns, the file encoding.

Right, so this is all of the data

that Power BI needs about the source.

The next thing that it looks at, as you can see here,

we've got column one, column two, column three,

is it says, "Hey, I recognize that the next row here

is actually the header row,

so I'm going to promote those to the header row."

Right, so now we have ProductKey, ProductSubcategoryKey,

ProductSKU, ProductName, and so on.

And if you look at all of these data types

on the left-hand side here, right,

they're all text base, right?

You can scroll over,

and you'll see that these are all text base.

So the next thing the Power BI does is it says,

"All right, based on the first 200 rows of this file,

I think that these are the most appropriate data types

to assign to each one of these columns."

So we've got our ProductKey here set up as an integer,

our ProductSubcategoryKey,

same thing, as a whole number or an integer value, right?

We've got ProductSKU as a text-based column.

We scroll over to the right here,

again, a bunch of other text-based columns.

And then we get to the end of the data set here,

and we see two columns,

ProductCost and ProductPrice, right?

And these are being assigned as decimal numbers.

Again, so Power BI is going through

and scanning the first 200 rows of this table,

and it's saying, "All right, here's my best guess

at what these different data types are."

So that's what those applied steps are all about.

Now, every time I connect to a brand new data set,

I always like to start with two steps.

The first step is to rename the file.

Right, so I'm gonna delete

the AdventureWorks portion of this,

and we're just gonna call it Product Lookup.

The lookup portion of this is going to be important

once we get to the data modeling section

Chris is gonna talk through why we name our tables that way

and how it's important.

The next thing that I like to do is really go through

and make sure that all of the data types here

are assigned appropriately, right?

And we've kind of already done that, right?

We confirmed that the product keys, subcategory keys,

these make sense as whole numbers.

Everything that looks like it should be

a text-based value is.

So we'll kinda scroll through these again.

And then the one thing here that we can do

is our ProductCost and ProductPrice columns,

these defaulted to a decimal number,

again, which isn't inaccurate,

but we can actually be a little bit more accurate, right?

So these are actually currencies, right?

And that data type within Power Query is referred to

as a fixed decimal number.

Right, so we could click Fixed decimal number,

and this is going to add a new step

and change our data type, right?

So instead of it being a decimal data type,

we're changing it to currency.

The other way that we could do this

is we're gonna delete that last applied step,

and I'm gonna select both of these columns.

And then I want to head up here to the Transform,

to my Data Type,

and then Fixed decimal number.

And we'll add this as a new step.

Right, and so now this new Changed Type 1 step is saying,

"Hey, we want to change

or we want to transform these column types

of ProductCost and ProductPrice both to Currency."

So really cool that we can do that.

One other thing that we could do here real quick

is we can right-click on the step and rename it.

And so let's rename this to

Changed to Currency.

Right, so now this is a little bit more readable,

and we actually understand what's happening here.

So overall things are looking really good.

Right, there really isn't too much that we would have to do.

We could probably load this file as is,

and it would be totally workable.

But let's say, for example,

that we want to remove this ProductSize column.

Right, when we start looking at it,

we can kinda see that it's a little bit of a mess, right?

We've got a mix between some numeric sizes

and text-based sizes.

And maybe we don't think that it's gonna be super helpful,

so we just want to remove it.

So again, there's multiple ways to accomplish this.

We can right-click the ProductSize column header

and click Remove, right?

Or we can come up and remove columns from our header tools.

Right, so Power Query adds a new step here

for Removed Columns.

We can see the associated M code generated here.

We'll just collapse that formula bar back.

And there we go. So it's really that easy.

So just to quickly recap, as a best practice,

try to get in the habit of updating your table names

and confirming your data types and their accuracy

every time you connect to a new data source.

It's really paramount for everything downstream of it

just to make sure that those are set up appropriately.

All right, so, so far, we have connected to this data set.

We've updated the table name.

We've confirmed our data types.

We updated a couple of data types to currency.

We've removed our columns.

So the next thing that we can do here is actually close

and apply these changes, right?

And what this does is this actually is going

to load the data into the data model,

and we'll see that in the front end of Power BI.

Right, so now that that's finished loading,

over here we've got both of our Product Lookup

and Territory Lookup data sets.

We can head over into the Data view,

and same thing, we can see the Product Lookup

and Territory Lookup data sets here.

And then if we jump over into the Data Model view,

I will scroll out real quick so we can see both tables.

And within the Data Model view,

we can see that we have added both the Territory Lookup

and Product Lookup tables here.

All right, congratulations,

you've just loaded your first two tables

into your data model.

From here, our last step is to really just save our work.

And from here, you're good to go.