Oz du Soleil makes the case for being able to pivot data in Get & Transform in Excel 2016. In and of itself, pivoting data in Get & Transform is harder than making a PivotTable in Excel. In this video, Oz shows a real-world case where it would be best to
- [Instructor] Now I'm going to show you what the big deal is with being able to do a pivot table inside Get & Transform. Being able to pivot in Get & Transform isn't a big deal in and of itself. It's much easier to create a pivot table in native Excel. However, when a pivot table is needed as an intermediate or final step in a query, that's when the pivot feature in Get & Transform is a lifesaver. Today I'm an admin who needs to compile the volunteer hours from two different locations, the city center office and the riverside office.
I just want a list of the people who participated, their hours, and their days. The riverside office looks perfect. The employee names are our row headers, the days are column headers, the hours they volunteered, there are values, perfect. The city center office is the mess. We've got to clean this up, the day column has holes in it, Cynthia and Joe have two rows, for Cynthia we would like to see five, not an entry for two and an entry for three.
To get this data stacked up, one we're going to have to do an append and two on the city center office data we're going to have to do a pivot. So let's get started. With the cursor in the riverside data set, go to Data, From Table. There's the data, close and load to, I'm loading it as a connection only because if I load it as a table it's going to be the same data, we don't need that.
Select only create connection and load. Next the city center office data. From Table, close and load as a connection only. Now I want to go back into the city center data, edit, and now let's do our clean up. The date column is already highlighted, right click, fill down in order to close the holes. Now every row has a day.
Next we're ready to pivot. The date column is already highlighted and that's important because we want to highlight the row that we want as our column headers. So let's go to transform, pivot. What do we want as our values? Hours volunteered, okay perfect, this looks exactly like the riverside data and we're ready to do the append. Let's go home, close and load.
It's still loaded as a connection. Now we do our append, new query, combine queries. Append. Right now we're appending these two tables, it doesn't matter which one is primary. Let's put riverside and then city center, okay. There's the data. Let's close and load that to the workbook. Got it. The data's all stacked up, it's been pivoted, notice that we do have Cynthia with five hours on Monday.
We don't have that two and three anymore. The key piece here is this was easy to do in getting transformed because we were able to create a pivot table as an intermediate step and then do our append and get what we wanted.
- 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