Practical Usage of Pivot Query and Pivot Pivot in Excel: A Complete Guide for Data Analysis
Introduction
Microsoft Excel is one of the most widely used tools for data analysis, financial reporting, accounting, and business management. As businesses generate increasing amounts of data, professionals need efficient ways to transform raw information into meaningful insights. Two powerful Excel capabilities that can support this process are PivotTables and Power Query.
The term “Pivot Query” is often used informally to describe combining Power Query with PivotTables, while “Pivot Pivot” may refer to creating multiple PivotTables, nested PivotTables, or using PivotTable features to analyze data from different perspectives. These techniques can work together to automate repetitive reporting tasks, summarize large datasets, and improve decision-making.
This article explains the practical usage of Power Query and PivotTables, how they differ, how they work together, and how to apply them in accounting, finance, sales, inventory management, and business reporting.
1. Understanding Power Query and PivotTables
Before exploring practical applications, it is important to understand the difference between these two Excel features.
1.1. What is Power Query?
Power Query is a data preparation and transformation tool available in Excel and Power BI. It allows users to import data from multiple sources, clean inconsistent information, combine datasets, and automate repetitive data-processing activities.
For example, an accounting team may receive monthly transaction files from twelve different branches. Instead of manually copying and pasting the files into one worksheet, Power Query can combine them into a single dataset using a repeatable process.
Common Power Query functions include:
Importing data from Excel, CSV, text files, databases, and other supported sources.
Removing duplicate records and unnecessary columns.
Changing data types, such as converting text into dates or numbers.
Splitting columns and merging information.
Filtering records based on specified conditions.
Combining tables using append and merge operations.
Creating calculated columns and transforming data into a standardized format.
Refreshing the transformation process when new data becomes available.
The main advantage of Power Query is that users can record a sequence of data-cleaning steps and reuse them whenever the source data changes.
1.2. What is a PivotTable?
A PivotTable is an Excel feature used to summarize, aggregate, and analyze structured datasets without writing complex formulas.
Suppose a company has 50,000 sales transactions containing the following fields:
Transaction date
Product
Customer
Salesperson
Region
Revenue
Cost
Profit
A PivotTable can summarize total revenue by region, profit by product, or monthly sales by salesperson in seconds.
The four main areas of a PivotTable are:
Rows: Categories displayed vertically, such as product names or departments.
Columns: Categories displayed horizontally, such as months or sales channels.
Values: Numerical measures to calculate, such as revenue, costs, quantities, or profit.
Filters: Criteria used to restrict the data displayed, such as a specific year or business unit.
PivotTables support aggregation functions including Sum, Count, Average, Maximum, Minimum, and other calculations, depending on the field and data configuration.
1.3. What does “Pivot Query” mean?
There is no single standard Excel feature officially called “Pivot Query.” In practice, the expression can refer to Power Query transformations that reshape data, the Pivot Column operation in Power Query, or a workflow that prepares data with Power Query and summarizes it with a PivotTable.
These approaches solve different problems:
Feature | Primary purpose | Typical example |
Power Query | Import and clean data | Combine twelve monthly sales files |
Pivot Column in Power Query | Reshape data | Convert month names into separate columns |
PivotTable | Summarize and analyze data | Calculate revenue by region and month |
Multiple PivotTables | Compare different views | Analyze revenue, cost, and profit separately |
Understanding these differences helps users select the appropriate method for each task.
2. Practical Example: Analyzing Sales Data with Power Query and PivotTables
Imagine a retail company that maintains a transaction-level sales report.
The original dataset contains the following records:
Date | Region | Product | Revenue | Cost |
01-Jan-2026 | North | Laptop | 2,000 | 1,500 |
02-Jan-2026 | South | Laptop | 1,800 | 1,300 |
03-Jan-2026 | North | Monitor | 900 | 600 |
04-Feb-2026 | South | Monitor | 1,200 | 800 |
05-Feb-2026 | North | Laptop | 2,500 | 1,800 |
06-Feb-2026 | South | Laptop | 2,200 | 1,600 |
The business wants to understand:
Total revenue by region.
Monthly revenue trends.
Profit by product.
Which region and product combination generates the highest revenue.
These requirements can be addressed through a combination of Power Query and PivotTables.
Step 1: Import the source data
Open Excel and select Data → Get Data.
Depending on the source, choose the appropriate option, such as From Workbook, From Text/CSV, or From Folder.
If the company stores monthly reports in a folder, the From Folder option can combine files that follow a consistent structure.
Select Transform Data to open the Power Query Editor.
Step 2: Clean and standardize the dataset
In Power Query, perform the following operations:
Remove unnecessary columns.
Remove blank rows and duplicate transactions where appropriate.
Convert Date into a Date data type.
Convert Revenue and Cost into numerical data types.
Standardize region and product names.
Check for missing values and inconsistent records.
For example, the values “North,” “NORTH,” and “North ” should be standardized if they represent the same region.
Create a custom column called Profit using the expression:
[Revenue] - [Cost]
This calculates the profit for each transaction before any additional costs or accounting adjustments.
Step 3: Load the cleaned data
Select Home → Close & Load to load the transformed dataset into Excel.
For larger datasets or more advanced reporting models, users can load the data into the Data Model instead of a worksheet.
The key benefit is that the data-cleaning process can be repeated when the source files are updated.
Step 4: Create a PivotTable
Select any cell in the cleaned dataset, then choose Insert → PivotTable.
Arrange the fields as follows:
Rows: Region
Columns: Month
Values: Sum of Revenue
The PivotTable will summarize revenue for each region and month.
For example, total revenue by region based on the sample data is:
Region | Revenue |
North | 5,400 |
South | 5,200 |
Grand Total | 10,600 |
The results are calculated from the six sample transactions. They demonstrate how transaction-level information becomes a management summary.
Step 5: Refresh the report
When new transactions are added to the source data, refresh the query and PivotTable as appropriate.
For a standard Excel Table used as the Power Query source, the query can retrieve newly added rows when refreshed. For a folder-based process, new files can also be incorporated when they meet the query's expected structure.
Depending on the workbook configuration, refresh the query and then the PivotTable, or use Data → Refresh All.
This workflow reduces repetitive manual work and makes monthly reporting more consistent.
3. Practical Usage of Pivot Column in Power Query
One of the most useful Power Query transformations is Pivot Column. It converts distinct values from one column into separate columns.
Consider this dataset:
Product | Month | Revenue |
Laptop | January | 2,000 |
Laptop | February | 2,500 |
Monitor | January | 900 |
Monitor | February | 1,200 |
The company wants each product on a separate row and each month in a separate column.
How to use Pivot Column
Open the dataset in Power Query Editor.
Select the Month column.
Choose Transform → Pivot Column.
Select Revenue as the Values Column.
Choose Sum as the aggregation function if multiple records exist for the same product and month.
Confirm the transformation.
The result becomes:
Product | January | February |
Laptop | 2,000 | 2,500 |
Monitor | 900 | 1,200 |
This is useful when preparing data for management reports, financial schedules, and applications that require a wide-format dataset.
However, Pivot Column and PivotTable are not interchangeable. Pivot Column changes the structure of the underlying dataset, while a PivotTable provides an interactive summary of the data.
When should you use Pivot Column?
Use it when:
You need one column for each month, department, or transaction category.
You are preparing standardized financial reporting templates.
You need to reshape data before loading it into another system.
You want to automate the conversion of transaction data into a reporting format.
For dynamic reporting, remember that the resulting columns depend on the distinct values found in the source data. New categories may create new columns after a refresh.
4. Practical Usage of Multiple PivotTables
The phrase “Pivot Pivot” can also describe creating multiple PivotTables from the same dataset to analyze different aspects of business performance.
Instead of forcing every analysis into one report, a company can create several PivotTables, each designed to answer a specific business question.
Example: Financial performance dashboard
Suppose the company has transaction data with Revenue, Cost, Profit, Region, Product, and Month.
Create three PivotTables.
PivotTable 1: Revenue analysis
Rows: Region
Columns: Month
Values: Sum of Revenue
This report identifies which regions and months generate the most revenue.
PivotTable 2: Profitability analysis
Rows: Product
Values: Sum of Revenue, Sum of Cost, and Sum of Profit
This report identifies which products contribute most to profitability.
PivotTable 3: Regional product analysis
Rows: Region, then Product
Values: Sum of Revenue
This report allows managers to expand or collapse each region to see the performance of individual products.
Together, these PivotTables provide a broader view of the business than any single summary.
Using the same source for multiple PivotTables
Whenever possible, build multiple PivotTables from the same Excel Table or Data Model. This helps maintain consistency across reports.
When PivotTables share the same PivotTable cache or compatible Data Model, users may be able to connect a single slicer to multiple PivotTables through Slicer → Report Connections or PivotTable Connections.
For example, selecting “North” in a Region slicer can filter several reports simultaneously.
This functionality depends on the PivotTables sharing a compatible source or model.
5. Advanced Technique: Combining Power Query with PivotTables for Monthly Financial Reporting
Monthly financial reporting is an excellent application of these Excel features.
Consider a finance department that receives general ledger extracts every month. Each extract contains:
Posting date
Account code
Account name
Cost centre
Entity
Debit
Credit
Currency
The department needs to prepare a monthly expense report by account, cost centre, and legal entity.
A practical workflow is as follows.
Stage 1: Consolidate monthly files
Use Power Query's From Folder functionality to combine the monthly general ledger extracts.
Standardize column names, data types, and file structures.
If the files have different formats, create appropriate transformation steps before combining them.
Stage 2: Calculate accounting measures
Create a calculated column for the net movement using the sign convention adopted by the reporting team.
For example, if expenses are represented as debit-positive amounts:
[Debit] - [Credit]
This calculation should be adapted to the company's chart of accounts and reporting conventions. Revenue, liabilities, and other account categories may require different sign presentation rules.
Stage 3: Create the financial PivotTable
Configure the PivotTable as follows:
Rows: Account code and account name
Columns: Reporting month
Values: Sum of net movement
Filters: Entity and currency
This produces an expense analysis by account and month.
Stage 4: Add management reporting dimensions
Additional fields can be added to the Rows or Filters areas to analyze expenses by:
Department
Project
Business unit
Legal entity
Expense category
A slicer can make the report easier for non-technical users to navigate.
Stage 5: Reconcile the report
Before using the PivotTable for financial reporting, reconcile its totals to the original general ledger or trial balance.
Check that:
All relevant source files have been included.
Duplicate records have not been introduced during consolidation.
Debit and credit signs are consistent.
Currency conversion has been applied correctly where necessary.
The reporting period and account filters are correct.
Power Query can automate data preparation, but it does not eliminate the need for accounting controls and reconciliations.
6. Using Power Query Merge and Append with PivotTables
Power Query provides two particularly important operations: Merge and Append.
6.1. Append queries
Append combines rows from two or more tables with compatible structures.
For example, a company has separate sales files for January, February, and March. Each file contains Date, Product, Region, and Revenue.
Appending the three datasets produces one consolidated transaction table.
This is ideal for:
Monthly reporting.
Consolidating branch data.
Combining departmental expense files.
Preparing historical sales analysis.
6.2. Merge queries
Merge joins two tables using one or more matching columns.
For example, the sales transaction table contains Product ID and Revenue, while the product master contains Product ID, Product Name, and Product Category.
Merging the tables adds product descriptions and categories to the transaction data.
The resulting dataset can then support a PivotTable showing revenue by product category.
When merging, check that the matching key has the intended level of uniqueness. If the lookup table contains duplicate keys, a merge can multiply rows and distort financial totals.
6.3. Why this combination is powerful
Append helps consolidate transactions, while Merge enriches transactions with additional information. PivotTables then summarize the resulting dataset.
This sequence is particularly useful when a company has several data sources but needs one consistent management report.
7. Practical Usage for Accounting and Finance Professionals
Power Query and PivotTables can improve efficiency across many finance activities.
7.1. Accounts payable analysis
A finance team can consolidate supplier invoices and analyze:
Outstanding payables by supplier.
Expense trends by month.
Payments by business unit.
Invoice volumes by supplier.
Overdue balances, if reliable due-date and payment-status fields are available.
Power Query can standardize supplier records, while PivotTables can summarize the results.
7.2. Accounts receivable analysis
A company can use these tools to analyze:
Receivables by customer.
Outstanding balances by ageing bucket.
Collection performance by month.
Customer concentration.
Overdue invoices by sales region.
Ageing buckets should be calculated using the agreed reporting date and appropriate due-date logic. A simple PivotTable cannot determine credit risk accurately without properly prepared data.
7.3. Budget versus actual analysis
Suppose the company has two datasets: actual expenses and budget expenses.
Power Query can merge them using common dimensions such as account, department, entity, and reporting period.
A variance measure can then be calculated as:
Actual - Budget
The resulting PivotTable can summarize:
Actual expenditure.
Budget expenditure.
Variance.
Variance percentage.
Variance percentage can be calculated as:
(Actual - Budget) / Budget
This formula requires special handling when the budget is zero or negative. The reporting team should define an appropriate presentation rule for these cases.
7.4. Cash flow analysis
Bank transactions can be imported and standardized using Power Query, then summarized with PivotTables by:
Transaction month.
Bank account.
Cash inflow and outflow category.
Business unit.
Currency.
However, bank transaction summaries should not automatically be treated as a complete statement of cash flows under IAS 7. Accounting classification, non-cash transactions, and reconciliation to financial records remain necessary.
7.5. Inventory management
Inventory transactions can be consolidated and analyzed to identify:
Quantity movement by product.
Stock receipts and issues by month.
Inventory balances by warehouse.
Product movement frequency.
Slow-moving stock, if reliable ageing or last-movement data is available.
For inventory valuation, additional logic may be required for methods such as FIFO or weighted average. A PivotTable alone does not calculate all inventory accounting requirements.
8. Practical Tips for Building Better Pivot Reports
To make PivotTables more useful and reliable, follow these recommendations.
Use structured source tables. Convert raw datasets into Excel Tables using Ctrl + T. Structured tables expand automatically when new records are added.
Keep the source data in a tabular format. Use one header row, avoid merged cells, and do not insert subtotal rows within the transaction dataset.
Use consistent data types. Dates should be dates, numerical fields should be numeric, and identifiers should be stored consistently.
Use meaningful field names. Names such as Net Revenue, Cost Centre, and Posting Date are easier to understand than Column1 or Field3.
Separate data preparation from reporting. Use Power Query to clean and transform the data, and use PivotTables to summarize it.
Avoid hardcoding monthly reporting steps. Build reusable queries that reference source folders, tables, or other stable data sources.
Refresh and reconcile before publication. Refreshing a query does not guarantee that all source records are correct or complete.
Use slicers and timelines where appropriate. They can make interactive reports more intuitive.
Consider the Data Model for larger datasets. It supports relationships between tables and can reduce the need to merge every field into one flat table. Power Pivot and DAX measures can provide more advanced analytical functionality where supported by the Excel version.
9. Common Mistakes and How to Avoid Them
Mistake 1: Treating Power Query Pivot Column as a PivotTable
These features perform different tasks. Pivot Column reshapes a dataset; a PivotTable aggregates and interactively analyzes it.
Solution: Decide whether the objective is to change the data structure or to create an analytical report.
Mistake 2: Incorrect aggregation
If multiple transactions exist for the same product and month, selecting the wrong aggregation can produce misleading results.
Solution: Use Sum for additive measures such as revenue and quantity, and select other aggregation methods only when appropriate.
Mistake 3: Duplicate records after merging
A merge may create duplicate transaction rows if the lookup table contains repeated matching keys.
Solution: Check the uniqueness of lookup keys and reconcile transaction counts and totals before and after the merge.
Mistake 4: Forgetting to refresh
A PivotTable may continue to show previous results if the source query or PivotTable has not been refreshed.
Solution: Establish a refresh procedure and verify the report's reporting date before publication.
Mistake 5: Building reports directly from uncleaned data
Inconsistent dates, names, currencies, and transaction types can lead to misleading summaries.
Solution: Standardize the data in Power Query before creating the reporting layer.
Mistake 6: Using PivotTables as a substitute for financial controls
A PivotTable can summarize incorrect data perfectly.
Solution: Include reconciliations, completeness checks, validation rules, and review procedures in the reporting process.
10. Power Query and PivotTables Compared with Excel Formulas
Excel formulas remain useful, but Power Query and PivotTables can be more efficient for repetitive reporting.
Requirement | Recommended approach |
Calculate a value for each row | Excel formulas or Power Query |
Combine monthly transaction files | Power Query Append |
Add descriptions from a master table | Power Query Merge or lookup formulas |
Convert month categories into separate columns | Power Query Pivot Column |
Summarize revenue by region | PivotTable |
Compare actuals and budgets | Power Query plus PivotTable or Data Model |
Create interactive management reports | PivotTables, slicers, and charts |
Model relationships across multiple tables | Data Model and Power Pivot |
Perform advanced calculations across related tables | DAX measures in Power Pivot, where supported |
The best solution depends on the task. These tools complement rather than replace one another.
Conclusion
Power Query and PivotTables are two essential capabilities for anyone working with data in Excel. Power Query simplifies the process of importing, cleaning, combining, and reshaping information, while PivotTables turn structured datasets into meaningful summaries for business analysis.
By combining the two, accounting and finance professionals can automate monthly reporting, consolidate financial records, analyze budget variances, monitor receivables, and build interactive management dashboards.
The practical principle is straightforward: use Power Query to prepare reliable data, and use PivotTables to analyze and communicate the results. For more advanced reporting, add the Excel Data Model, Power Pivot, DAX measures, and appropriate financial controls.
Mastering this workflow can significantly reduce manual reporting effort and allow professionals to spend more time interpreting business performance rather than repeatedly preparing spreadsheets.
Resources : Internet
Comments