Original subtitles

Instructor: Next up, we've got tech specific tools.

And looking at the Transform tab and the query editor,

you'll notice that Power BI groups different sets

of tools together based on their purpose or function.

So in this case, we'll find all

of our tech specific tools grouped together

at the end of the ribbon.

And within this group

we've got some really interesting and powerful options.

So for one, we can split

up a column based on a specific character

or delimiter or based on a number of characters.

We can format any of our text columns

using basic formatting options,

like lowercase, uppercase, proper case,

which is capitalizing the first letter of each word.

Or we can use tools like Trim, which eliminate leading

and trailing spaces, or clean, which does the same thing

and also eliminates non-principal characters.

Now you might be thinking that those trim

and clean options really aren't that helpful,

but once you run into a case where you

have one trailing space in your data set, and trust me

this will drive you crazy the first time you experience it.

The problem is, as human beings

we're incapable of seeing a trailing space.

It's completely invisible to us

and it looks exactly the same

as any other data point without the trailing space.

But to Power BI, it looks

like a completely different and unique value.

So in cases like that,

trim or clean can be great tools just to standardize

and help avoid issues like that,

especially if you're working

with really messy or unstructured text data.

We also have some great extract tools.

We can extract a certain subset of characters

from a string based on a specific length.

We can extract the first

or last number of characters or a range.

But where it gets really interesting is using delimiters,

which we've already used as part of our first assignment.

So what we can do here is we can actually tell Power BI

that we want to return all of the characters

before a specific delimiter or symbol or character

or after it, or between two distinct delimiters.

And there are also some advanced options as well

that allow you to specify whether you search

from the left side of the string or the right

and if you want to skip a certain number

of instances of each delimiter before returning the text.

So just some really great flexibility there

with those extract tools.

So you may have noticed that some

of the tools here are grayed out or inactive,

like the merge columns or parse.

And that brings up a really important point,

which is that this toolbar is completely dynamic based

on what you've selected.

So if you've only selected a single column,

the merge column options are irrelevant,

so you can't even click them.

You'd have to select multiple columns

in order to activate that option.

And then taking that even further

if you've selected a column that's numerical instead

of text, this entire group of tools might be replaced

by numeric based or number specific tools instead.

So just remember that the entire ribbon, the entire toolbar

that you're seeing here will dynamically change based

on your selections.

Now, one more very important point to highlight

before we shift gears into Power BI,

anytime you see this yellow box that says,

hey, this is important,

I'm gonna talk about something really important.

So that means focus in and pay attention

because this is gonna be something that'll come

up time and time again throughout the course.

And it's really, really important

for you to fully grasp and understand.

So what I want to talk about now is the difference

between transform and add columns

because this is something that confuse me for a while

and it's really important to understand the difference

between the two.

And what I was noticing was that the same tools, in fact

the same identical sets

of tools kept popping up in different places.

And for the longest time, I just thought

that this was really confusing and redundant,

until I realized that the outcome is completely

different depending on where you select the tool.

So when you select a tool from within the Transform tab,

you're essentially modifying

or overriding the column that you've selected.

But when you choose a tool from the Add Column tab

you're creating a brand new column within your table.

So that may sound really obvious when I say it now,

but I guarantee as you're learning this tool

and as you're playing around with the query editor, you will

at some point in time select the tool from the wrong tab.

And you know what?

That's okay because obviously nothing is set in stone.

It's as simple as just deleting the last applied step

and you'll be back where you started.

But it is something to keep in mind

and hopefully it will help you at least understand

where you've gone wrong

and why these tools appear in multiple places.

So with that, let's head back to Power BI

and we're gonna get our hands dirty with some

of these text tools.

So continuing with the customer lookup table,

one of the things that's been bothering me

about this table is the prefix and the customer name

and first name columns.

So if I scroll back over here, right, everything's

capitalized and it just it looks pretty jarring

to me when I look at this.

I would like to see this in a proper case.

Let's first update our prefixed column, right?

So we'll come up here to add column,

and we're gonna come down to format.

And within the format menu option we have see our lowercase,

uppercase, and then capitalize each word.

This is the proper text option, right?

So we can click here and oh, so all right, so we've actually

added a new column to the end of our data set here

and it's called Capitalize Each Word,

which is a a pretty bad title there.

But at any rate, what we've done here is instead

of selecting this from the Transform tab, we've actually

selected the format option from the Add Column tab.

So again, we're adding a brand new column to the end

of the data set.

And again, just to highlight like this is what I'm talking

about, it's really easy to just select the wrong menu item

and create a brand new column instead

of transforming a column or vice versa.

You could do it the other way around.

Again, the great news here is that it's easily fixed, right?

We can just come in here, click X to delete that,

apply to step, we'll come back over to our prefix column

and now we can head up to our transform menu.

And then from here, head over to our text column tools,

click format, capitalize each word,

and now we're in the right spot.

We've transformed the values in this column

to a proper case, or actually only capitalizing each word.

All right, so the next thing that I'd like

to do is also apply that same transformation step

to the first name and last name columns as well.

And so what I could do is I could select each one

of these one at a time, come up to format

or I can multi-select by holding shift and click,

come here to format, capitalize each word.

And then the Query Editor creates those applied steps

to change first name and last name both to proper.

Awesome, so we're in good shape there.

The other interesting piece here to call out is

we didn't add a new applied step here

for capitalize each word.

If we actually look at the M code here, we can see

that we're transforming the columns to proper text, right?

Text dot proper.

And what Power Query has done is it added two

more conditions here for first name

and last name after prefix.

Right, so we're just kind of adding

or mashing that code together into one applied step

because the transformation is all the same there.

All right, so now that all

of these customer text attributes are updated

to proper case., what I would love to do

is create one brand new column for customer full name.

All right, and you can see here again,

we're in our transform tools.

So we want to head over to add column.

And what I want to do is I wanna select prefix, first name,

and last name.

So again, click on prefix hold shift

and select last name to select all three.

One thing to call out here is

that selection order matters when you're kind of merging

or combining things within Power Query, right?

So if you had selected last name first

and then prefix Power Query, interprets that

as you want the last name column first

and then first name in the middle, and then prefix last.

So just make sure you're selecting the columns

in the order that you want them combined.

So from here, and we're gonna come up to merge columns.

And again, here we're just setting our separator.

We're gonna add a space in between each one of these

we can create a new column name.

This is an optional step,

but again merged here is a pretty bad name.

So we'll call this full name, click okay and awesome.

You can see that we have our new insert merged column

applied step here.

Scroll to the end of the data set.

We've got our full name column where we have our prefix,

our first and last name.

And then the last thing that I want to do here

is just rename this applied step

to something a little bit more readable

so we can remember it.

Again, we don't have a lot going on right now

but this may change over time.

All right, so I'm gonna update merged

to full name and we'll apply that change.

All right, so with those updates, I think we're pretty good

with our customer lookup table here.

Last thing is let's head back to the Home tab

and I wanna click close and apply to apply these changes

and load them into our data model.

All right, so now that that's finished loading,

can scroll over and you can see

that we have our new table here for customer lookup.

The other thing that's interesting and to take note

of is that the tables that we have marked

as disabled load are that we're not loading

into Power BI data model, like our SQL Connections

they're not showing up here, and that's expected.

So it's just great to confirm that

and understand exactly what that functionality does.

All right, so I'm gonna save this

and I will see you in the next lecture.