Hello and welcome to this video.
In this video, we are going to be enhancing our query once you have cleansed your data.
There are ways to enhance your query, making it easier to analyze later on.
Let's take a look at the results files we're going to use for this video.
I've got it saved on my desktop on a folder called Resource Files.
So these are the files where you're going to have the file called Enhance.
And the original CSP file we connected to called IVA Cells.
These files are going to be available for download on the resource area.
For this video, please download it and save it on your desktop so you can follow along with this lecture.
Thank you.
I'm going to double click to open the file called Enhance and they will launch the file.
You may notice this yellow background warning that says Protected View shows.
Click to enable editing.
It may launch on that window, giving you a security warning.
Just click to enable the content.
Next, click on the query option here from the Excel menu and then click on edit and that will open
up the Power Query Editor.
So any enhancement we make to our query is going to be made inside this window.
If you take a look at the data here, you'll notice that for every transaction there is a make and there's
also a module.
So let's do some transformation here.
So click to select the MC column and then from the menu here, select transform.
While Transform is selected, I want to capitalize the content of the make column.
So make sure you've got the Transform tab selected and then right click from inside the column here.
And this should be an option for transform.
So Select Transform and then select uppercase and that will capitalize the records in that column.
So if you take a look at the applied steps on the right here, the step we just took has been added
to the applied step.
Next, let's go to the first column here.
Click on that, which is the clean alternative for very cold type.
You notice we have some acronyms here in brackets.
So I'm going to click on this dropdown.
Inside the column, we have two values.
We've got the battery electric vehicle, which has an acronym abbreviation of BTV, and then we've got
the plug in hybrid electric vehicle.
We've got the abbreviation of PTV.
Now these abbreviation does not actually add any value to the data analysis process.
So I'm going to apply a step to remove them.
So I'm going to click okay to exit that in the last step where I made the record in the Mac column capitalized,
you can also do it via the format option here.
Notice there's a format tab.
So you could also why you had the column selected, you could also click on the dropdown and there is
an option for uppercase.
So there are two ways you could have done that, either through the format or by right clicking inside
the column as I did.
The next thing I want to do is to remove the acronyms from the first column.
So we've got BGP and so on.
I want to remove all of the B these and the P here TV to do that.
I've got the columns selected inside the menu here.
There's an option for extract.
So click on the extract and select this option that says text before Delimiter.
Select that, and that will launch a dialog box in the dialog box that has appeared.
So click to go into it.
Type A space on your keyboard and then add an open bracket.
And click.
Okay.
So when you click okay, you would notice that all the acronyms in brackets here will disappear.
So I'll click, okay.
And you noticed the brackets that contained acronyms have all disappeared.
The step we've just done has been added to the Applied Steps list, so you can see this extracted text
before Delimiter.
The next thing I want to do is to create a new column that will merge both the make with the module.
This is sometimes referred to as concatenation.
And the reason I want to merge these two columns together is that we might just want to analyze them,
make the model as one column later on in our analysis.
To begin, click on this add column tab here, double click and then click on the custom column option.
And that will give you a dialog box for the custom column, give you a custom name.
So I'm going to change the custom name to something more meaningful.
I've changed the default name for the column.
I've changed it to make slash model.
And then notice on the right here, we have available columns now inside here on the custom column formula,
I'm going to create a formula.
So basically I want to create a formula that will allow different fields to be joined together.
So to begin, I'm going to in the available columns option here, I'm going to double click on the make
and I will add that to the custom formula.
If I double click, you can see it has inserted that into the formula area.
The next thing I want to do is to type in an ampersand and then followed by a double inverted commas.
And in between the inverted commas, I'm going to add a column and some space.
So make sure in the formula window you've got the double inverted quotes, and in between the mix of
the spaces between the quotes, and then you have the colon.
Now, after the last quote.
I'm going to add another ampersand.
And then I'm going to in the available columns here, I'm going to double click on the module, so double
click on the module and up pops the module in the.
So what this formula is going to do, it's going to create a new column that combines the make on module.
So click okay.
And then you'll notice that on the far right here, a new columns being created called makes large model.
And if you look under the applied steps, the step we've just taken, there's been added added custom.
So let's go ahead and load the changes we have made into our data model.
So click on the home tab here and then on the the close and load, click on the dropdown and select
close and load and that will load the changes into our data model.
So this may take a while because it contains 150,000 records, over 150,000 records.
So it can take a while.
Please be patient.
So the query that we prepared and enhanced has been applied to our data, including the new column that
we have created.
So if I scroll down to the far right, you see the new column we created called Make Slash Model, which
was as a result of concatenation.
Concatenation basically means when you join together.
So we joined two columns together to make one column.
In this video, we performed some extra steps to enhance our query.
Thank you for watching.
Bye for now.