Our first example takes two tables of sales data and appends (or stacks) them into a single table. This newly stacked table will act as a feeder dataset for a Pivot Table.
The trick is to keep the original tables separate while at the same time not physically creating the feeder table. We want the appended results to be fed directly to the Pivot Table for reporting.
Bringing in the Two Data Sets
The first data set is for Store 1 and appears as follows.
We see the Transaction Number in the 1st column and the Sales Amount in the 5th column.
The second data set is for Store 2 and appears as follows.
Notice that the Transaction Number in the 2nd column and the Sales Amount in the 4th column.
This won’t be a problem because our tool of choice will rearrange the data based on column names before sending it to the feeder table.
Meet your new best friend, Power Query
Power Query is a built-in Excel tool used to transform data that would otherwise be unusable into perfectly usable data.
Although not required, Power Query works more efficiently if the source data is in a proper Excel Table format.
To convert our two existing “plain” tables into proper Excel Tables, click anywhere in the first table (Data Store 1) and press CTRL-T on the keyboard. A Create Table dialog box will appear. Click OK to select the defaults.
On the Table Design ribbon, click in the Table Name field (far left side of the ribbon) and rename the table “TableStore1” (no spaces).
Repeat the above process for the second table (Data Store 2) and rename the upgraded table “TableStore2” (no spaces).
Using Power Query to “stack” the two data sets
Next, we bring the two tables into Power Query.
Power Query will be used to append (i.e., “stack”) the two tables into a single table.
A great feature of the Append process is that the column’s order in the tables does not need to be identical. Power Query will automatically rearrange the column order of each table to align into a single table.
Because we need to remember which transactions are sourced to which store, we will add a “helper column” that contains the store’s number next to each related transaction.
Step 1: Bring the first table into Power Query
Click anywhere in the first table (“TableStore1”) and select Data (tab) -> Get & Transform Data (group) -> From Sheet (formerly named “Table/Range”).
A new window will open showing the Power Query Editor.
One small adjustment to this data would be to discard the time information in the “Date” column.
This is easily done by selecting the “Date” column header then click “Data Type: Date/Time” from the Home ribbon and change the data type to “Date”.
Click “Replace Current” in the message that follows. This will update the existing data typing step instead of creating a whole new step for this similar transformation.
Step 2: Add tracking information for each transaction
To not lose focus of which transactions came from which stores, we will add a column to the table that has the store number attached to each transaction.
On the Add Column ribbon, click Custom Column (far left of ribbon).
In the Custom Column dialog box, give the new column the name “Store” and enter the following formula in the larger formula field.
The result is a new column (far right of the table) that displays “Store 1” for each transaction.
As with the “Date” column, change the data type for the newly added “Store” column to a TEXT data type.
Step 3: Bring the second table into Power Query
We can save a few steps here by making a copy of the “TableStore1” query and making a few targeted changes.
If you click on the SOURCE step in the “Applied Steps” panel (right of editor window) you can see the reference to the “TableStore1” table.
We will make a copy of this query and change the SOURCE step’s pointer from “TableStore1” to “TableStore2”.
In the Queries panel (left of editor window), right-click the “TableStore1” query and select Duplicate.
Rename the duplicate query from “TableStore1 (2)” to “TableStore2”.
Select the SOURCE step in the “Applied Steps” panel.
In the Formula Bar, change the entry from “TableStore1” to “TableStore2” and press ENTER to commit the change.
Don’t forget to update the “Added Custom” step at the bottom of the Applied Steps panel to use the label “Store 2” in the “Store” column.
Step 4: Close the Queries
Because these two queries will ultimately send their results to a 3rd “stacked” query, we will finish their creation process by loading them to Excel.
Because we don’t wish to create actual output tables of these two queries, on the Home ribbon, click the lower part of the “Close & Load” button and select “Close & Load to…”
In the Import Data dialog box, select the option labeled “Only Create Connection” and click OK.
You will now see two queries on the right side of the screen in the Queries & Connections panel.
Step 5: Append/Stack the Tables into a Single Table
To append the two tables into a single table which will be used to drive the Pivot Table, click Data (tab) -> Get & Transform Data (group) -> Get Data -> Combine Queries -> Append.
In the Append dialog box, select the “Two Tables” option, then select each table from the two supplied dropdown fields. Click OK when complete.
Back in the Power Query Editor, you will see a newly created query named “Append1” with the results of the two previous query’s tables stacked to form a single unified table.
Note how we can keep track of which store each transaction came from by using the created “Store” column.
Step 6: Rename the appended query and output to an Excel Table
Let rename the appended results query to “AllStores”, then click the lower part of the “Close & Load” button and choose “Close & Load…”
In the Import Data dialog box, select Table and New Worksheet as the destination options and click OK.
The result is an Excel Table with the appended results.