Oz du Soleil addresses the common need to bring multiple data sets together into one by stacking them all up—formally called appending data. See how easily this is done in Get & Transform.
- [Instructor] Here you'll see how to stack up multiple data sets. Here we have three different data sets representing three different sessions of mandatory fun that our company hosted. Our goal now is to get everybody matched up into one big list. But first let's notice a few things. One, all three data sets have a name column. Two sets have a department column, and two sets have a work location. We'd like all of that lined up wherever it's appropriate.
Our data sets are already in tables, we just have to now create queries so that we can append those three queries. Let's get started with the cursor in the data set, and with the Data tab in the ribbon selected. We make a query from Table. Here's our Session1 query that we're just going to close and load as a connection. Load, Close and Load To. Only Create Connection. Load.
And we see on the right side in the Workbook Queries pane, the Session1 query is a Connection only. Let's make the other two. With the cursor in the next data set, From Table, Close and Load, Only Create Connection. Finally, From Table, Close and Load, Close and Load To, Only Create Connection. Now we see we have a three query as Connection only, let's append them all.
Let's go to New Query, Combine Queries, Append. Okay now we have to select this radio button, Three or more tables. Now we options on how we want to stack 'em up. Let's stack 3 on top first. Add, then 2. Add, 3 Add. OK. Ha, and here is our data. Let's close and load these to the workbook.
And here's our data all swept together in one big list. It does look kind of peculiar that the name is in the second spot. Let's go back into our append and change that. Append, Edit. Just grab the name, slide that over. Close and Load. Name is in the right space, all three data sets are stacked up. And we did that with the Append, Three or more tables, and get in Transform.
- 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