Hướng dẫn dùng pivot table Informational, Transactional năm 2024

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.

Creating a Pivot Table Report from the Appended Data

To create a Pivot Table from the appended tables, perform one of the following actions:

• Right-click the “AllStores” query in the Queries & Connections panel (right) and select “Load to…” In the Import Data dialog box, select Pivot Table Report and New Worksheet as the destination options and click OK.

…or…

• Click in the table of results and select Insert (tab) -> Tables (group) -> PivotTable.

You can now create a Pivot Table to answer your business-related questions, such as, “What were the total sales by store along with the percentages of total sales?”

Quickly Connect Two Tables Using a Relationship

In this example, we want to leave the tables in their original structures but connect them in such a way that they “talk” to one another.

Our first table named “TableData” has transactional information.

Our second table named “TableMaster” has descriptive information about each product in our store catalog.

Traditionally, any column in the catalog table that needs to be reported on against the “Data” table (ex: “Total sales by Department”) would need to be brought over from the catalog table to the transaction table using a function like VLOOKUP or a formula that used INDEX & MATCH.

This would create a single, large table.

What we will do is connect these two tables using what is known as a Relationship.

What is a Relationship?

A Relationship is where you select a column from each table that has identical information.

For example, we have a “ProductCode” column in the “TableData” table. This lists the item sold.

We are likely to see many repeats of the same product codes as we hope to sell many of the same items throughout the reporting period.

In the “TableMaster” table, we have an entry for each of the product codes along with descriptive information about each product.

The catalog of products should only have one entry for each product.

Connecting the Tables (i.e., creating the Relationship)

To connect the two tables by way of the ProductCode column, perform the following steps:

1. Select Data (tab) -> Data Tools (group) -> Relationships.
2. In the Manage Relationships dialog box, click “New…”
3. In the Create Relationship dialog box, select the two tables using the left-side dropdowns and the “ProductCode” column using both right-side dropdowns. Click OK when finished.

You now have a connection between each table that serves as a live communications path between the tables.

Click the “Close” button to close the Manage Relationships dialog box.

This relationship adds the two related tables to what is known as the Data Model. Think of it as a back-end database of all related tables.

To create a Pivot Table from the two related tables, select Insert (tab) -> Tables (group) -> Pivot Table (dropdown arrow) -> From Data Model.

Place the Pivot Table on a new sheet.

Populate the Pivot Table as needed to answer the applicable business questions.

“Which is better: Appending or Relationships?”

Neither one of these approaches is better than the other when discussing technique. However, each approach brings to the table a series of pros and cons.

Selecting the “correct” approach depends on how the data is structured, what you want to create, your comfort level with Power Query and/or Relationships.

It’s up to you to decide the method you prefer when dealing with your data.

Knowing and becoming proficient in both methods will serve you well in the long run.

In Microsoft 365 there’s also a formula alternative for appending or stacking data. Check out this tutorial on the VSTACK function.

Practice Workbook

Feel free to Download the Workbook HERE.

Published on: July 1, 2021

Last modified: April 24, 2023

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.