Original subtitles

Next up I want to talk about grouping and aggregating data.

So within the transform tab of the query editor you see this group by option on the left.

And this allows you to aggregate roll up your data at different levels of granularity.

So some 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 by 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.

I know it's a little bit tough to see but what we've got here are 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 product number to 14 has been ordered multiple times on multiple dates by multiple different customers.

And if you wanted to transform this table into a summary of orders order quantities rolled up by unique

product keys the group by option is a great way to do that.

So when you click that group by button you'll see a dialog box that looks something like this that basically

allow you to tell power be-I.

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 that aggregation how do we want to treat those order quantity values.

In this case we're taking a simple sum and we're ending 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.

So to recap what we've done is essentially transform a daily transaction level table into a summary

of total quantity rolled up by product keys.

And important thing to note here.

As you may have noticed any fields that aren't specified in our group by settings are lost they're not

preserved in that final table because we eliminated that level of granularity in the process.

Now one more example using the advanced option.

Consider the same exact table that we started with reusing the same group by option.

But this time looking at the advanced version and really the only difference here between basic and

advanced is that advanced allows you to specify multiple Collins or additional columns to group by.

So this time instead of just grouping by product key or grouping by product key and customer key and

again we're evaluating that same operation.

The some of the order quantity values just like before.

This time what we end up with is a three column table 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.

So the best comparison that I can make if you're an Excel user this is 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.

So that's grouping and aggregating data.

In a nutshell that's actually work through an example or two in power be-I.

So back in power be I mean my adventure works report.

Instead of adding new data I'm going to edit my queries to jump into the query editor.

Got my four connections my four tables here.

I'm going to dig into my A.W. sales 20:17 data which contains daily sales records broken down by product

keys by customer keys territory keys.

So this is a good candidate for using these grouping or aggregation tools.

So just like our demo Let's go ahead and select the product key column and let's say we want to turn

this entire table which contains multiple product keys and multiple instances of product keys which

we can see if we sort these as you can see multiple sales for product number 214 and so on and so forth

for all of the other products in this table.

There's a lot here so I have to scroll quite a bit to get to them but take my word for it there are

many many more product ideas in here.

So the idea is that we want to take this table collapse these rows with multiple product keys into essentially

a summary table and just like our example in the slide let's evaluate the sum of order quantity for

each of these product keys.

So go ahead and transform click group by Remstar with our basic option here grouping by product key.

The new column name which is the column that contains the values that are getting aggregated or rolled

out we can call it whatever we want let's say total quantity for example.

And again that operation you have different statistical functions here different aggregator functions

could take the some you could average order values you could translate them all to a max or men you

could count the rows in this case we want the sum and we want the sum of that order quantity and press

OK.

So just like our demo this collapsed our data into a two column table with that new aggregated quantity

field that we've named total quantity and a unique list of product keys that can test that they're unique

by sorting them.

And as you can see there's only one instance of each of those IDs with the associated total quantity.

Now let's do one more example.

I'm just going to get back and remove those applied steps and I want to do one more group by example

with the advanced options instead.

So let's select product key again.

Group by click the Advanced button.

And now we can add a second grouping here for whatever other fields we want to pull into this summary

table.

So in this case maybe customer maybe territory choose customer key here because I want to see the total

sales for every combination of product and customer.

And again my column name could be something like total quantity and it will be the sum of the order

quantity.

OK.

And there you go.

Very very similar process here.

It's now collapsed my table into a three column table containing unique combinations of product key

and customer key.

So there you have it.

Once again let's go ahead and remove those steps.

We don't need this sort by product key.

And then in fact if we don't even want to save anything that we've done in here we don't even need to

go to the close and apply button.

We can simply close out of that query editor.

So there you go grouping and aggregating data.