Power Query vs pivot tables: when to use each (with a real case)
They are the two Excel tools most often confused, because both "transform" data. The difference is one of stage: Power Query prepares (imports, cleans, combines) and the pivot table summarizes (groups, computes, compares). Using them in that order turns a one-day close into a ten-minute one. We explain it with a real case of sales by branch.
In this article
- What each one does, in one line
- Comparison
- Real case: sales from twelve branches every month
- Step 1: Power Query imports the folder
- Step 2: Power Query cleans once
- Step 3: Load as a table and summarize with a pivot table
- Result
- Three frequent mistakes
- And when to move to Power BI
- Want to bring this to your team?
What each one does, in one line
Power Query is Excel's query editor: it connects to files, folders and databases, applies cleaning and transformation steps that stay recorded, and loads the result as a table that refreshes with one click. The pivot table takes an already-clean table and summarizes it by the categories you choose: sales by month and branch, average per product, share by region.
The practical rule: if the problem is that data is dirty, incomplete or in several files, it is Power Query. If data is already fine and the problem is summarizing it to answer a question, it is a pivot table. You almost always need both, in that order.
Comparison
| Power Query | Pivot table | |
|---|---|---|
| Stage | Prepare the data | Summarize and analyze |
| Input | Files, folders, databases, web | A clean table (ideally loaded by Power Query) |
| What it does | Clean, change types, split, append, merge, pivot/unpivot | Group, sum, count, percentages, compare periods |
| Repeats every month | Yes: steps stay recorded; one click refreshes | Yes: refreshes with the source table |
| Typical misuse | Using it to "look at" data instead of loading it | Summarizing dirty data and getting duplicate categories |
| Level where it is taught | Advanced Excel / data analysis | Intermediate Excel |
Real case: sales from twelve branches every month
A retail chain received twelve Excel files every month, one per branch, with slightly different formats: in some the date was text, in others the "Branch" column said "CDMX" or "Cd. de México", all had total rows at the bottom. The close consisted of copying the twelve sheets into one, correcting by hand and building a pivot table that showed fourteen branches instead of twelve. It took a day.
Step 1: Power Query imports the folder
Instead of opening twelve files, Power Query connects to the whole folder and combines every file into a single query. If next month there are thirteen, it picks them up on its own.
Step 2: Power Query cleans once
Change the date column type, remove total rows (filter by "Total" in the product column), replace "Cd. de México" with "CDMX", trim spaces. Every step stays recorded; next month it applies itself.
Step 3: Load as a table and summarize with a pivot table
The query loads as a table in the workbook and the pivot table is built on it: sales by month and branch, variation versus the previous month, each store's share. Now it shows twelve branches, because the data is clean from the source.
Result
The close goes from a day to "refresh all" and review: ten minutes. What matters most is not the time but that the process is reproducible and does not depend on whoever did it by hand. It is the central exercise of the data analysis with Excel course, done with each company's real files.
Three frequent mistakes
- Cleaning in the pivot table. Manually grouping "CDMX" and "Cd. de México" inside the pivot table works once and is lost on refresh. Cleaning belongs in Power Query.
- Using Power Query to summarize. You can group in Power Query, but every change of question forces you to edit the query. Summarizing is the pivot table's job, which changes view with a drag.
- Loading the query to a sheet and then copying it. If you copy the result, you lose the refresh. The pivot table must point to the table loaded by the query (or to the data model).
And when to move to Power BI
When sources are many, readers need different permissions or the file is already too heavy. The good news: Power Query is the same in Power BI, and the pivot table becomes visuals on the same model. The data analysis learning path chains exactly that road.
Want to bring this to your team?
We ask for your real files before the course and the Power Query and pivot-table case is built with your own month-end close. Message us on WhatsApp, fill out the contact form or open this site's live chat: we send a proposal with three scope options and a closed price in under 24 business hours, with the DC-3 certificate and Geedle Bot included. The full program is at the data analysis with Excel course and the catalog at training courses for companies.
