Original subtitles

Instructor: Up next, I want to talk about

grouping and aggregating data.

So within the transform tab of the Query Editor

you'll see this Group By option on the left

and this allows you to aggregate

or roll up your data at different levels of granularity.

So one of the common examples of this

would be doing something like transforming

daily transactions into weekly or monthly

or rolling up transaction level data by store, by product,

brand or region, et cetera.

So it's taking a really deep, really detailed table

and rolling it up into a higher level summary.

So let's take a quick look at an example.

Here we've got order quantities by order date, product key,

and customer key.

And the important thing to note here

is that we have multiple orders for a given product key.

In this case the product number 214

has been ordered multiple times on multiple days

by multiple different customers.

And if you wanted to transform this table

into a summary of orders

or order quantities rolled up by unique product keys,

the Group By option is a great way to do that.

So when you click the Group By button

you'll see this dialogue box that looks something like this

that basically allows you to tell Power BI,

hey, I'd like to group this table by unique product keys.

And the operation that I'd like to evaluate

for those product keys is the sum

of the order quantity column.

In other words, you're taking all of these duplicate rows

with the same product key and multiple order quantities

and you're compressing them down to one.

When you do that compression or aggregation

how do we want to treat those order quantity values?

Now in this case, we're taking a simple sum

and we're gonna end up with a table like this,

two columns, one containing unique distinct product keys,

and the second containing the total quantity

or the sum of order quantity values

associated with each product key.

So to recap, what we've essentially done is transform

a daily transaction level table into a summary

of total quantity rolled up by product keys.

And the important thing to note here

as you may have noticed,

any fields that we aren't specifically calling out

in our Group By settings are lost.

They're not preserved in that final table

because we've eliminated that level of granularity.

Now, one more example, using the advanced option

and let's consider that same exact table

that we started with.

We're using the same exact Group By option

but this time we're looking at the advanced version.

And really the only difference here between the basic

and advanced is that advanced allows you to specify

multiple columns or additional columns to group by.

So this time, instead of just grouping by product key

we're grouping by product key and customer key.

And again, we're evaluating that same operation.

The sum of the order quantity values, just like before.

This time, what we end up with is a three column table

with product key, customer key and total quantity.

So to recap, we've again transformed

that daily transaction level table

now into a summary of total quantity

aggregated by both product key and customer key.

In other words, we're now looking at quantities

by each unique combination of those two fields.

And the best comparison that I can make to this,

it's really just like creating a pivot table

and pulling in the sum of order quantity

into your values and your product key

and customer fields into your row labels.

All right, so that's grouping and aggregating data

in a nutshell.

Let's jump over to Power BI

and we'll actually work through an example or two.

All right, so back in my Adventure Works report.

And instead of adding new data

we're gonna jump right back to the Query Editor

and we're gonna edit the sales data table.

All right, so if we select our sales data 2022 table

which contains daily sales records

broken down by product and customer keys, right?

Territory keys, right?

This is a great candidate for using Group By

and aggregation tools.

So just like in our demo

we're gonna select the product key here, all right?

And let's say that we want to group this entire table

which contains multiple product keys

and multiple instances of product keys, right?

And we can see that if I sort ascending, right

we've got all of these different product keys

that are repeating for different orders,

and we could scroll down here

might take a while if we scroll, but trust me, right,

there's a lot of different product keys

contained within this table.

So the idea here is that we want to take this table,

collapse these rows with multiple product keys

into essentially a summary table.

And just like the example on the slide

we want to evaluate the sum of order quantity

for each one of these product keys.

So from here, what we wanna do is head up

to the Transform tab, and then we're gonna click Group By.

And we're gonna start with our basic option here.

So we want to group by product key

and for our new column name

let's call this something like Total Quantity

and the operation that we want to perform here

we have a bunch of different options

for some different statistics tools

or aggregation functions.

So sum, average, median min, max, et cetera, right?

And we want to find the sum of our order quantity column.

So we'll press OK,

and just like our demo

this collapsed our data into a two column table

that's now aggregated by the quantity field

with the associated product keys, right?

And we have a unique list of product keys here

which we can see again if we sort this column, right?

So instead of having multiple repeated values

and rows for product 214.

We have a single row for 214, 215, 220,

and so on.

Now let's do one more example here

and I'm actually gonna remove these applied steps here.

So we'll select our product key column again.

Come back up, we'll group by,

and this time I wanna select the advanced button, right?

So what this again allows us to do is add a grouping here

and you can add any other sort of fields

that you want to pull in from the table, right,

by date, order number, customer key, territory key,

you know, so on.

And here I'm going to select customer key

because I wanna see the total sales

for every combination of product and customer.

And again, we can call this total quantity.

The operation we'll do a sum, and we're gonna base that

on the order quantity column.

We click OK.

And now our result changes.

We now have a three column table

containing the unique combinations of product key,

and we'll sort this, right of product key, customer key,

and our total quantity, right?

So now you can see that we have multiple instances

of product key because we've added another dimension here,

our customer key.

So there you have it.

And once again, let's go back

and we'll remove these applied steps

for grouping and sorting.

And at this point,

we actually don't need to save any of this

because we don't wanna load any of these changes

into the data model.

We really haven't made any sort of significant changes.

So no real action is needed here.

All right, that's gonna wrap up your crash course

on grouping and aggregating data.