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.…
AuthorOz du Soleil
- 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
Skill Level Intermediate
1. What Is Get & Transform?
2. Working with Queries
3. Working with Columns
4. Working with Formulas
5. Pivoting and Unpivoting Data
Group data2m 31s
7. Appending a Query
8. Merging Data with Joins
9. Putting It All Together and Applying Your Learning
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.