← All Kits · Excel Kit

Power Query or Power Pivot: Which One Does the Thing You Are Trying to Do

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

Two tools, both bolted onto Excel, both with Power in the name, and they do entirely different jobs. The confusion costs people months, because the wrong one turns a fifteen minute task into a week of nested formulas.

What you do: everything that changes the shape of the data goes in Power Query, before it lands. Everything that measures the data goes in Power Pivot, after it lands.

The short version. Power Query is the kitchen. Power Pivot is the till.

The dividing line

Power Query is best at these four

1. Combining files. Point it at a folder and it appends every file in it. Twelve monthly extracts become one table, and next month's file joins automatically.
2. Unpivoting. A report with twelve month columns becomes a long table with a month column, in two clicks. This is the single highest-value thing Power Query does, because almost all awkward Excel data is wide when it should be long.
3. Cleaning that must repeat. Trim, split, change type, remove the top four junk rows. The steps are recorded, so next month you press refresh instead of remembering.
4. Merging tables. A proper join with a chosen join type, rather than a lookup down a column.

Power Pivot is best at these four

1. Relationships. Several tables joined once in a model, rather than lookups copied into every table.
2. Measures reused everywhere. Define Revenue once and every pivot in the file uses that definition. When it changes, it changes in one place.
3. Calculations that respond to filters. Share of total, year to date, same period last year. These are hard in a normal pivot and natural in DAX.
4. More rows than a worksheet holds. The model is not the grid, so the million row limit from the row limit page does not apply.

The order of operations

Power Query, then the Data Model, then measures, then the pivot. That order is not a preference, it is the flow of the data, and building against it is where slow workbooks come from.

Source file  ->  Power Query (clean, reshape, combine)
             ->  Data Model (tables plus relationships)
             ->  DAX measures
             ->  PivotTable / chart on the sheet

The classic mistake is loading raw data to a sheet, then bolting 200,000 SUMIFS onto it. Every one of those recalculates constantly. Move the same work up into the query and it happens once per refresh.

Which one fixes your problem

Getting started with each

Power Query. Data, Get Data, From File. Do one transform, then look at the Applied Steps list on the right. That list is the whole idea: a recorded, editable, re-runnable recipe. Delete a step and the data goes back.

Power Pivot. Data, Manage Data Model. Add tables, drag a key from one to another to build a relationship, then create a measure with Revenue := SUM(Sales[Amount]). If a measure returns the same number in every row of a pivot, the relationship is missing or pointing the wrong way.

How to apply this to your own work

1. Open your slowest workbook and count the formulas that repeat down a column. Those are candidates for the query step.
2. Take the monthly file-combining job you do by hand and rebuild it as a folder query once. It pays for itself the first month.
3. Find any lookup column you have pasted into more than one table and replace it with a relationship.
4. Write down your three most-used calculations and turn them into measures with names people can say out loud.
5. After each change, refresh and check a total against the old version. Moving work between layers is exactly where numbers quietly shift.

The one habit to keep

Ask where a calculation belongs before you write it. Load time or read time. That single question keeps the model clean and it is the same question you will answer again in Power BI, where the two tools are the same two tools with different names on the tabs.

What is the oldest repeated cleaning job in your week, and could a query do it once?