[MUSIC PLAYING] Welcome to MSPTDA
video number eight. Hey, in this video, we're still
talking about Power Query group by. But wait a second, I thought
that's what we did last video? Yes, but we want to see how
to use group by to either create an unique list with
some aggregate calculation or this amazing ability--
an aggregate calculation on consecutive occurrences. Now, let's go look at a picture. Here's our data set. And if we grouped by type
day and aggregate sales-- this is what we saw last video. But guess what? We can change one
of the settings. And instead of
doing an unique list of all of the items from type
day and then at aggregation, we could actually group
up all of the workdays that happen consecutively-- get those two
numbers for work day. Then go on to weekend
aggregate, holiday aggregate, and all of these work days. We will end up with whatever
aggregate calculations we want for consecutive
occurrences. So let's go over to Table.Group
fourth argument-- this sheet. There's our data set. I have already imported
it as an Excel table so I'm going to double
click this query. There's the name of the query. I can come over to
the left and click and open up my list of queries
in this Excel workbook. Now, you can either rename the
query on the left or the right. I have this selected
so I'm going to hit F2 and I'm going to rename this
as day type sales global. Now that will be the
setting we use when we want to do a standard group by. I'm going to expand
the column a bit there. Now we come over to type day-- right click, Group By. We definitely want
to group by type day. We're going to do total sales. And then I'm going to arrow
up to get the sum function. And the column we
want to sum is sales. Now I'm going to click
Advanced and I want to do a second aggregation. I'm going to say count rows-- count rows. That aggregation will be
fine, nothing there is needed. Now I click OK. There is our standard
group by, unique list, and our aggregations. Now let's come over
to Applied Steps. Click on Group Rows. Come up to the Formula Bar. We'll expand-- there's
our Table.Group function. The first argument is
always the previous step. The second argument is, which
column do you want to group by? And then the third argument
is list within a list. There's our first aggregation. There's our second aggregation. And within the list
within a list-- the name of the column,
calculation, and data type. It is the fourth argument
that we want to look at. Now if I come back up to Group
Rows and double click that-- I don't see anything in the
user interface that allows me access to this fourth argument. So that's where
we're going to Cancel and just edit our M code. Now before we edit
our M code, we're going to learn something
really important about M code in Power Query. Notice, group rows--
we click the icon and it opens up our dialog box. Well, because there's
no fourth argument available in the dialog box-- when I edit this up here,
that Gear icon goes away. And the only way we
can edit the code is up in the Formula
Bar or Advanced Editor. We will no longer be able to
use that Gear icon to get back to our dialog box. So fourth argument--
I'm in a type comma. And this is called GoupKind. When I do dot, I can choose
either global or local. Now, global is the
default. And watch what happens when I hit Enter-- nothing will happen here, but
over in our applied steps-- that Gear icon will go away. So when I hit Enter,
nothing happens here because that's the
default. Over here-- uh-oh-- that is gone. No problem. We want to edit our
code and use local. But for the time being, I
want to leave GoupKind.Global. Now if you really were
using the default, we would not put this in. But just to illustrate, we're
actually going to load this. Now I want to
close and load this so I'm going to
click Close and Load. This has already been loaded
so when I click Close and Load, it's a connection only. To put the report
in this sheet-- I come over, right
click, Load To to change the load to location. Table-- I want it
in H16, click OK. Now I want to copy the
code that generated this and change the fourth argument. So I come over, double click
to open up the query editor. Over on the left,
expand Queries. Now I want to copy this. Right click-- and there's
two ways to copy-- duplicate and reference. Now reference is
not what we want. If I click Reference, we can
see up in the Advanced Editor-- all it did was take the name
of the query and repeat it. That means if I
change anything here, it will be reflected
in this second query-- that's not what we want. Click Cancel. Click and Delete key
or right click Delete. We want right click Duplicate. What Duplicate does is-- if we look up into
the Advanced Editor-- it did exactly what we wanted. It duplicated all of the code. Now I'm going to click Cancel. Group Rows is selected so
up in the Formula Bar-- GroupKind. Double click Global
and type local. When I hit Enter, that's it. Fourth argument local. Now we get group by
consecutive occurrences. Now I want to come over-- F2-- to rename this. Something, like,
local and Enter. Now this has not been loaded
so I'm going to click Close and Load, Close
and Load to Table, Existing-- we'll try L16. Click OK. And there, we have
made aggregations based on consecutive occurrences. Now let's change this up. These two right here
were not work day. I'm going to hold Control. Neither were these two. This is something called-- and I'm going to type in
the active cell right here-- donation. These are days we worked
and donated everything to a good cause. Now to populate the
item in the active cell to all the selected cells,
I hold Control and Enter. Now I want to refresh
all of our queries-- Data, Refresh All. Or I can use the keyboard-- Control-Alt-F5. And look at that-- there's our unique
list and aggregation. Here's our consecutive
occurrences with aggregations, including donation, donation. Unique list and aggregation--
that's global, the default. Aggregation based on
consecutive occurrences-- use the fourth argument in
Table.Group, global Kind.Local. Now be sure and if
you're interested-- try the practice problem
or the homework problems. Every single video
in this series-- or almost all of
them-- have problems that you could practice. And the one for this
video is pretty fun. So check it out. All right, if you'll
like that video-- be sure to click that Thumbs Up,
leave a comment, and subscribe. And when you subscribe, you
got to click that Bell icon. YouTube used to notify everyone,
but they don't do that anymore, unless you click that Bell icon. I even recently had
to go through all of my favorite YouTubers
and click that Bell icon. Next video-- not 8.5, but video
number nine in this series-- we'll talk all about M code. All right, we'll
see you next video.
