All language subtitles for 4. Demo Basic Table Transformations in Power BI

All right.

So let's kick things off let's start.

Nice and easy with table transformations.

Now if you go to the query editor look in the Home tab you'll see all sorts of basic table transformation

options.

Let's talk there's some of the most important ones.

First off you can choose to keep or remove certain columns or rows from your table.

There are a few interesting options here.

Looking at the Columns options you can either select the column that you'd like to remove or select

the column that you'd like to keep.

Now that might not seem like a really important distinction but it can be really helpful in certain

cases.

So let's say you've got a data connection to a table that might have a variable number of columns one

month that might have 10.

The next month it might have 20 but at the end of the day you always want to land with 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 end up in your final table which you

don't want.

On the other hand if you select the five that you always want and you say hey power by these are the

five columns I need anything else that you find in the table remove it then you'll be good to go moving

forward.

So it's a bit of a nuance but actually a really helpful tool when you need to do something like that

as far as the rows are concerned you've got some basic options here.

Remove the top rows or bottom rows.

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

This is really helpful if you have files that might have had a rows or footers or grand totals that

you need to strip out other options here you can remove alternate rows or you can remove duplicates

that can be a great way to actually create new lookup tables from scratch by creating unique lists of

ideas for example.

You'll also find some basic sorting options a dizzy LOW to HIGH etc. as well as options to change your

data types or promote your header rows.

Now power because pretty smart about headers by default it should identify and promote those header

rows for you.

But in case it doesn't.

You can always do that manually using this tool here.

And then last thing to call out if you right click any column header you can access a lot of these same

common tools plus 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 multiple menus multiple tabs sometimes

even multiple views within the power by file.

So with that let's open up a brand new workbook and connect to our first data source.

All right.

So I've opened up a new blank power by file.

Go ahead and do the same.

And make sure that you follow along carefully with what I'm about to demonstrate because what we're

doing is actually connecting to the source files that we're going to use to build our project throughout

the course.

So as you work through these demos and through the homework exercises you'll be building up all of the

pieces that will need to assemble into our final project by the end of the course.

So let's get started by clicking on the Get Data dropdown.

This allows me to access the most common options in this case.

We want a text or CSP file and what I've done here is create a folder called Power by course files on

my desktop and then within that I've added a new folder called Adventure works.

And this is where I've saved all 10 of those CSP files that are available for download as part of course.

So in this case the file we want to work with first is adventure works underscore products.

So go ahead double click to open that one up now as soon as you've selected a file.

The first view that you'll see is this preview window.

This has a couple options worth paying attention to.

It will automatically detect the file origin as well as any delimiter in place.

In this case comma is correct.

Since this is a CSP file and then this data detection dropdown basically says OK do you want power RBI

to take a little sneak peek at your data and take a guess at what these data types should be.

If so do you want it to base that guess on the first two hundred rows.

Do you want it to be based on the entire dataset.

Or should we not detect data types at all in which case you can manually add those data types to your

columns in the query editor.

So by default let's just keep it on the first 200 rows.

Usually does a pretty good job detecting the correct data types.

And now from here you can just load straight into your workbook.

You can bypass the query editor entirely as a best practice.

I would recommend against doing that even if you don't think you'll need to make any sort of transformation

to your data launching the query.

Ed is a great way to just keep away and make sure things look good.

So let's go ahead and hit edit which will open up the query editor All right.

So here we are we're in query Ed..

We've got our one lonely query here.

Adventure works underscore product which you can see has been named based on the file name itself the

same name has been applied to the table and venture works product.

And take a look at this.

There are already three appliance steps that have been made and all we did was double click on the file.

We haven't done anything else so let's actually click back through and see what power RBI has done here.

And if we expand this formula bar so we can see the M code we'll get a sense of what's really going

on here.

So the first step the source step.

This will always happen and this is just power by saying OK let's go find the file location and let's

pull it in.

In this case we're looking at a path on my C drive and it's finding the folder on my desktop and grabbing

the adventure works products CSP file.

Now I think this goes without saying but your path will look different unless your name is also Chris

and you've created the same exact file on your desktop.

So that's step one the source which makes sense.

But look at the preview here.

Notice how we've got these empty placeholder column headers and then our first row contains the actual

column headers.

So the next applied step as you may have guessed identifiers it detects those headers and promotes them

to the first row and then from there the third step looks like it's just changing some data types based

on the detection that we talked about in the preview window.

So jumping back and forth you can see it's changed some text strings into whole numbers numerical values

which looks good.

So that's pretty cool.

RBI has already done quite a bit to help clean up this data set for us.

And now from here there are always two steps that I start with in the query editor every time I connect

to data every time I load up a table I start with these two steps.

Step one is updating my table name.

So let's go ahead and select this and update it to a W underscore product underscore look up and I'll

tell you more about why I'm using look up in the table name.

Once we get to the data modeling one one section but that should do the trick a W product look up you

can press Enter to lock that in and the second step that I always take in the query editor is to scroll

through my column headers and make sure my data types and column headers look good.

So let's go ahead and just collapse this formula bar we've got product key just integers.

Got subcategory key and then a whole bunch of text fields product SKU name model name product description

color the size the style.

And then two more numerical fields here.

The cost and the product price which are decimal values.

Now we could update these last two to currency or fixed decimal numbers but I'm going to show you how

to do that in a separate place later on.

So don't worry about these two just yet.

Now things look pretty good here.

I don't really need to do much to this table at this point it's pretty clean.

But let's say we don't really want the product size column.

It's kind of messy.

You know we've got a mix of numerical sizes as well as text based sizes like large medium small.

So let's just remove this column.

And like I mentioned on the slide there are a couple of ways to do it can either use the remove columns

option here in the Home tab or it can right click the column header itself and click remove.

And when I do that it's added a new appliance step and remove that column.

So that's just about all that I need to do here.

As far as transforming this table is concerned if I wanted to it looks like my product keys are sorted

low to high.

But if we wanted to make sure we could go ahead and use the sorting options to do that here and I think

that just about does it.

So we've applied some table transformations to our product table.

We've given it a meaningful name and we've confirmed that the data types and column headers look good.

So all that's left to do is press this close and apply button and it will go ahead and load it into

our file.

So now we can see on the right side in our fields list in the report view we've got this H.W. product

lookup table with all the fields listed out.

You'll also see it in the data view.

Here we go the same table same fields.

And last but not least it will appear as a new object right here in our relationships view as well.

So congratulations.

We just created our first data connection in our workbook.

Why don't you go ahead and save this workbook.

I'm going to call it adventure works underscore report you can name it whatever you want.

And this is a power by file dot PBX extension press save and you are good to go.