Oz du Soleil shows a simple but incredibly useful method to group data in Get & Transform. This is a cleaner and much easier way of generating subtotals than in native Excel.
- [Instructor] Let's do some grouping and getting transformed. If you want to follow along, pull up the grouping exercise file. Here we have data about items in two stores. We would like to know the total value of the inventory by item, by store. Not by detail or department. The challenge is to get a unique list of the items. Once we've got that unique list, then we can do our grouping and get our totals.
So let's start a query and get to work. With the cursor inside the data set, let's go to from table. Here's our data. To get this cleaned up and ready, the store column is already highlighted. Right click to close these gaps. Fill down. Next, we need to separate the item detail column because we just want sofas, gloves, caps. We don't need leather, stool, office, driving.
Let's highlight that column. Split column by delimiter. It defaults to comma. And it's okay to leave this at each occurrence of the delimiter, okay. See we have our items and details separated. Let's get rid of this column. Remove. Rename as item. We didn't want the department data. Right click remove that. Now we're ready to do our grouping.
Let's go to group by. Advanced. Advanced allows us to group by multiple criteria. So the first criteria, let's group by the store. Group by store, add a grouping. Group by item. And what we want to know is the total value. Our new column name is going to be total value and we want to do a sum on the total column.
Okay. Close and load that. Let's make it larger. Now it's easy to see that the Riverview store has 740 dollars worth of caps in inventory. And that was easily done by using grouping inside getting transformed.
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins