This video continues from our last video and shows you how you can cling shape
and combine your data using Power BI. Keep watching, so here if you look at the
shape of this data it spread across these columns and this is a
human-friendly shape but not a machine friendly because it's hard to instruct
the model later on to say, hey! give me a sum of the budget you have to say oh
yeah some this column and this column and this column and keep going that's very
hard to do. What we prefer is that instead of these months being on columns
that they were on rows instead now this operation is called unpivot, I didn't
really care to understood why it's called that but that's how I knew it and
I knew it in my Excel days and I felt so proud of myself that I knew this trick
of how to unpivot data quickly and the trick was that I would load the data to
clean Excel into a sequel table into a sequel server and then I'll crack open
my sequel book, look up the unpivot command because I would
always forget the syntax, type it in, get the results, and save them back to an
excel file. So it took me a while to do it but I thought man how neat that I can
do it and then I discovered Power Query and the first time I encountered this
and I realized that all I have to do is just click this button click unpivot
column, boom! done. Right so the data again you can play it back data was spread
across in columns January through December unpivot, and now it's organizing
rows. So now when I send it back to the data model and we're going to look at
measures later on to sum the budget I just have to sum this column. Let's clean
up a few things here, so for this one, we can just double click or you can right
click and say rename which I can never find and I'll say this is the month
and I'll just double-click and say this is the budget amount. Now you notice that
there are these icons next to each of these columns and this one abc 123 is
really alphanumeric so it can be anything this one it thinks it's the
text value and this one it thinks its alphanumeric as well. So clearly, that's
not quite right and the easiest way to change is to actually click that icon
you can click that icon and choose what you want it to be so this
one I want it to be a date because later we're going to connect to the calendar
table you're going to learn all about that right so this is a date now notice
the icon changed and again the change type step was recorded. Now notice
something interesting here I'm going to change this value so I'm going to say
budget amount and actually I'll point out something else to you so you may not
see this when the first time you start working with Power BI desktop in the
query editor you may have noticed that I pointed out the ribbon I pointed out the
panel over here the data pane itself, but I didn't mention another element which
you see here which is called the formula bar and this is really interesting and
you may not see by default because this needs to be turned on so if you go to
the View tab you can toggle it off or on by default off but I think its really
really cool and even if you don't care about it for now
you probably don't beginners you should still turn it on because it just gets
you in the habit of looking at it, getting slightly familiar with it,
because eventually you're going to start stepping into it and mucking with it and
it's not as hard as it seems really a lot of it for one makes sense I mean if
you read it it kind of makes sense right, so it's a change type it's saying table
dot transform colon types, okay makes sense it's trying to change the column
type transform colon types, bring in columns it actually is referring to the
previous step here rename columns okay go there and then change the month to
type date right, so make sense. All right, so let's do another step here and we're
going to go to budget amount, and change that to fixed decimal
number or currency and notice what happens here, we do not get an extra step
instead, it combined it in the same step and it kind of makes sense so it's
efficient that way it's doing the right thing it's a-okay
if you go ahead and change types of five or 20 columns I'm not going to add five
change type steps I'm just going to keep one, and it's going to say change month
to type date, change budget amount to currency, and so on so forth, right so
it's pretty cool it does the smart thing alright the product key needs to
be numeric as well and we'll talk about relationships later on but really you
can examine it right here as well, a budget table is eventually going to
connect to the product table and if I go to the product table and look at the
product key this one is numeric I can see the data table data type right here
using that icon. So I'm going to make sure that this one is numeric as well
otherwise my relationship would not work, so let's make it a whole number and
that's it and now we're ready to go. So I'm going to click home you can also
click just close and it will prompt you to apply but we're going to come back to
the home tab in the ribbon and click close and apply. All right, so we go back
and again it does it goes out connects to that data source however it's being
defined applies all the transformations and pulls it in. So now if I save this
file again, let's see what the file size is I expect it to grow slightly. Now my
table was really small so maybe we wouldn't notice that much of a
difference let's go back and save it and if you go back here yep it grew slightly
from three one seven six to three nine three seven six six I think two three
nine four three right so any data you pull in a copy of it is made here. Now
let's go back and check out a few things in our query
well actually hold on so let me show you that here indeed, we have the new table
budget table is right here and I can examine it look at it and it also shows
up in the relationship view let's go back and into a query's pane, let's talk
about a few more things... Hey, keep watching more videos and keep
learning Power BI. But if you did enjoy this video I would love to
hear from you so leave a comment, like, subscribe, all the good stuff. Power on
my friends.