Original subtitles

Instructor: Welcome to the assignment

solution walkthrough.

As a quick reminder,

Ethan reached out to us,

asking if we could connect to two new tables,

our Product Category Lookup

and the Product Subcategory Lookup,

and also make some transformations

to the Product Lookup Table.

So, here's a solution preview of what you should see

in the query editor once you've completed the assignment.

Keep in mind that depending on how you approached

solving Ethan's request,

your applied steps may look

a little bit different than mine,

and that's totally fine.

The most important thing

is that you end up with four tables in the Query Editor

and the proper transformations made to the tables.

So, with that, jump on in.

All right, so up first,

we needed to create two new queries

to connect to our Product Category Lookup

and our Subcategory Lookup.

So, we're gonna go back to our AdventureWorks file

and I'm gonna grab the Product Category Lookup.

Right? Very basic table here.

Click OK.

This loads into the Query Editor.

And then I'm gonna come back

and we're gonna connect to the Subcategory Lookup now.

All right, same deal here.

Three column Lookup table here.

Data preview looks good.

And great, so, we've got both of these tables loaded in.

Next, we need to update the Product Category

and Product Subcategory Lookup table names.

So from here, we'll remove the AdventureWorks piece,

and again, product space subcategory space lookup.

Looks good.

We'll lock that in.

And then same deal here for the Product Category Lookup.

All right, our third key objective

was to confirm that the column headers have been promoted

and that all data types are correct.

So, from the Category Lookup table here,

we have a Product Category key.

All right, this is set as a whole number,

so that looks good.

And then we have a text data type for our category name.

So, everything looks good here.

We'll check the Product Subcategory real quick.

And again, at first glance, everything looks great here.

Our column headers have been promoted.

We have our correct data types.

Right, whole number, text,

another whole number for our Product Category key.

All of that checks out.

Our fourth key objective here is to add a new column

to extract all characters before the dash

and product SKU and name it SKU Type.

So for this,

we've gotta head to our Product Lookup table.

Right?

And we need to add a new column

that's gonna be based on Product SKU.

So we'll click the Product SKU column,

head up to Add New Column.

Wanna head to this Extract option.

And then we're gonna do Text Before Delimeter.

Right? So what we wanna do

is we want to extract all the characters

before the dash and Product SKU.

So this is gonna bring up a dialogue box,

and all we need to do is enter the delimiter here,

which is that first dash.

So we've added a new applied step here.

Insert a Text Before Delimeter.

New columns are added on the far right of the table.

So we've got our column here

with the first two values extracted,

and now, we can double-click and rename this SKU Type.

All right, so that's our fourth step.

Fifth, we need to update the SKU Type calculation

to return all characters

before the second dash instead of the first.

So what we can do

is come back over here to our applied steps,

and if we click on this little gear icon,

we have Advanced Options here, right?

And then scan for the the delimeter

from the start of the input,

and then number of delimeters to skip, right?

So we're gonna skip the first instance.

So we've returned everything before the second dash.

Again, we'll kind of scroll back.

Rename column and then perfect.

Right, now we have all of the values

within that column returned before the second dash

instead of the first.

All right, so our sixth key objective here

is to replace zeros in the Product Style column with NA.

So we'll click on the Product Style column,

head up to the Transform menu, and then Replace Values.

And again, this is gonna launch a dialogue box here.

The value to find is going to be zero

and we wanna replace that with NA, right?

So we can click in here

and see that all of those zero values

were now replaced with NA.

And we'll cancel this.

And now our last step is to close

and load this into the data model.

So come back Home, click Close and Apply.

So, we can see we've got our four tables

all loaded from our Report View.

And see the same thing in our Data View.

Lookup Subcategory, our Territory Lookup.

And then if we go back into our Data Model View,

again, I'll scroll out here,

and you can see all four tables loaded.

All right, so, perfect.

Make sure that you guys are saving your workbooks

as you go along.

And that's gonna wrap it up

for this assignment solution walkthrough.