Power Query (Data tab, Get Data) is Excel’s tool for importing data from files, folders, databases or web pages, and cleaning or reshaping it through a recorded sequence of steps, before it ever reaches a worksheet. The Data Model (built with Power Pivot) is a place to hold several tables together, linked by relationships, so a PivotTable can pull fields from more than one table at once without a helper column joining them by hand.
Both tools are about handling data that plain formulas do not scale to well: large imports, data spread across several tables, or a cleaning process you need to repeat every week on fresh data. This guide explains what each one is for, and where the line falls between what a formula can do and what these tools are built for instead.
In this guide
The short version
- Power Query imports and reshapes data before it lands in your sheet, through a list of recorded steps you can edit, reorder or remove.
- Every step is written in a language called M, though most people never type it: dragging, clicking and choosing menu options builds it for you.
- The Data Model holds several tables linked by relationships (usually one-to-many, matching an ID column), so a PivotTable can use fields from all of them together.
- Refreshing a query re-runs its recorded steps against the current source. It does not write anything back to that source.
What Power Query is for
Open it with Data, Get Data, choosing a source: a file, a folder of files, a database, or a table already on a sheet (From Table/Range). Power Query opens the Power Query Editor, showing a preview of the data and a panel of buttons for changing it: removing columns, splitting one column into several, filtering rows, changing types, grouping, and more. Choosing Close & Load sends the result to a worksheet or the Data Model.
The advantage over doing the same cleaning with formulas is that the steps are recorded once and apply to any future version of the same source. A weekly export that always needs the same five cleaning steps can be refreshed with one click instead of being cleaned by hand every time.
Applied Steps: a recorded, repeatable recipe
Every action you take in the editor appears in the Applied Steps list on the right, each one named after what it did (Filtered Rows, Changed Type, Removed Columns). Click any step to see the data as it looked at that point, which makes the editor a kind of undo history you can also edit: click the gear icon next to a step to change its settings, or delete a step from the middle of the list, and everything after it re-runs automatically.
Behind the scenes, each step is one line of the M language, chained together with let ... in. You can see and edit this directly in the Advanced Editor, but almost every day-to-day task is built by clicking buttons, the same way a macro recorder builds VBA without you writing it by hand.
Common transformations
| Task | Where in the editor |
|---|---|
| Remove exact duplicate rows | Home, Remove Rows, Remove Duplicates |
| Split one column into several | Transform, Split Column (by delimiter or by position) |
| Combine several columns into one | Transform, Merge Columns |
| Turn column headers into row values | Transform, Unpivot Columns |
| Turn row values into column headers | Transform, Pivot Column |
| Join two tables side by side, matching a key | Home, Merge Queries (like a VLOOKUP, but keeping every column) |
| Stack two tables with the same columns | Home, Append Queries (like copying one table under another) |
| Group and summarise, like a mini pivot | Transform, Group By |
Merge Queries is worth calling out because it is the direct equivalent of a lookup: instead of adding one VLOOKUP column that brings back a single value, a merge brings back an entire matching table, columns and all, and you choose a join type (only matching rows, all rows from the left table, and so on) the way a database join works.
The problem a relationship solves
Before relationships, the standard way to bring a lookup table’s columns onto a sales table was a VLOOKUP column, repeated for every extra field you needed, and repeated again if a second sheet needed the same lookup. Here Sales holds a ProductID, and Products holds the matching name; the VLOOKUP column has to be added, and would have to be added again anywhere else this table is used:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | OrderID | ProductID | Amount | Product (via VLOOKUP) | ProductID | ProductName |
| 2 | 1 | 101 | 500 | Pen | 101 | Pen |
| 3 | 2 | 102 | 300 | Book | 102 | Book |
| 4 | 3 | 101 | 700 | Pen | 103 | Bag |
| 5 | 4 | 103 | 200 | Bag |
A relationship in the Data Model does the same matching once, centrally: link the two tables’ ProductID columns, and every PivotTable built from the model can then use ProductName straight from the Products table alongside Amount from Sales, with no extra column anywhere and no risk of the lookup formula being copied incorrectly or left out of a new sheet.
The Data Model and Power Pivot
To build a Data Model, load two or more tables with Add this data to the Data Model ticked (in the Get Data or Create PivotTable dialogs), then open Power Pivot, Manage to see them together and draw relationships between them by dragging a column from one table onto the matching column in the other. A relationship is usually one-to-many: one row in a lookup table (one product) matches many rows in a transaction table (many sales of it).
Once the tables are related, Insert, PivotTable, and choosing Use this workbook’s Data Model gives you a single field list containing every table’s fields together, as if they had always been one table.
A first idea of DAX measures
A PivotTable built on the Data Model can use ordinary sums and counts of a table’s own columns, exactly like a normal PivotTable. For anything more, the Data Model has its own formula language, DAX (Data Analysis Expressions), used to write measures: for example Total Sales = SUM(Sales[Amount]), or Sales Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])). DAX looks like an Excel formula but works across the whole related model rather than one sheet, and the RELATED function is the DAX equivalent of following a relationship to pull in a field from the other table.
DAX is a large topic on its own; the thing worth remembering here is that it exists specifically because the Data Model can hold several related tables, which plain worksheet formulas were never designed to do gracefully.
Refreshing: Power Query pulls, it does not push
A query only changes when you tell it to: Data, Refresh All, or right-click a query and choose Refresh. It re-runs every Applied Step against whatever the source currently contains. Editing the loaded table on the worksheet directly does not feed back into the source, and refreshing overwrites those manual edits with a fresh copy from the source. Keep manual adjustments in a separate column or a separate query step, never by hand-editing the loaded results.
Common mistakes
Mistake 1: editing the loaded table instead of the query
Any change you make on the sheet is lost the next time the query refreshes. If a fix needs to survive, add it as a step in the query.
Mistake 2: not checking the join type in Merge Queries
A merge defaults to keeping only matching rows unless you choose a different join type. Rows that do not have a match on either side can silently disappear.
Mistake 3: repeating the same VLOOKUP column on every sheet
If several PivotTables or sheets all need the same lookup, a relationship in the Data Model does it once, centrally, instead of a formula copied everywhere.
Mistake 4: expecting a query to write back to its source
Power Query is read-and-transform, not read-and-write. It never edits the original file, folder or database it reads from.
Cheat sheet
| Task | Tool |
|---|---|
| Import and clean a messy source | Power Query (Data, Get Data) |
| Repeat the same cleaning every time data refreshes | Applied Steps, edited once |
| Join two tables like a lookup, keeping every column | Merge Queries |
| Stack two similar tables | Append Queries |
| Combine fields from several related tables in one PivotTable | Data Model with relationships |
| A calculation that spans related tables | a DAX measure (Power Pivot, Manage) |
| Update everything after the source changes | Data, Refresh All |
Try it yourself
Think through each answer; these are about which tool fits, more than a single number.
1. A weekly CSV export needs the same five cleaning steps every time. What is the advantage of Power Query over doing it by hand in a fresh sheet each week?
Show solution
The five steps are recorded once as Applied Steps. Refreshing re-runs them automatically on the new file, instead of repeating the cleaning by hand.
2. Two tables, Orders and Customers, share a CustomerID. Name two ways to bring the customer’s name onto the Orders table, one using an ordinary formula and one using the Data Model.
Show solution
A VLOOKUP or XLOOKUP formula added as a column on Orders, or a relationship between the two tables in the Data Model, letting a PivotTable use fields from both without any extra column.
3. You manually correct three cells in a table that was loaded from a Power Query. The next morning someone refreshes the query. What happens to your corrections?
Show solution
They are overwritten, because the refresh re-runs the query’s steps against the source and reloads the result from scratch. Corrections need to be added as a query step, not made on the loaded table.
4. What is the DAX equivalent of a plain SUM formula, and what makes it different?
Show solution
A DAX measure such as Total Sales = SUM(Sales[Amount]) looks similar, but it lives in the Data Model and can be used by any PivotTable built on that model, across every related table, not just the one sheet it was typed on.
Frequently asked questions
What is Power Query used for?
Importing data from files, folders, databases or the web, and cleaning or reshaping it through a recorded, repeatable list of steps before it reaches a worksheet.
What is the difference between Power Query and a formula?
A formula recalculates values already on a sheet. Power Query imports and reshapes the data itself, and can pull from external sources a formula cannot reach, with steps that are recorded once and reused.
What is the Data Model in Excel?
A place to hold several tables together, linked by relationships, so PivotTables and DAX measures can combine fields from more than one table without a manual lookup column.
What is a relationship in the Data Model?
A link between a column in one table and a matching column in another, usually one-to-many, that lets Excel treat the two tables as connected without merging them into one.
What is DAX?
Data Analysis Expressions, the formula language used to write measures inside the Data Model. It looks similar to Excel formulas but works across related tables rather than a single sheet.
Does refreshing a query change the original source file?
No. Power Query only reads from the source and writes its result into the workbook. It never writes back to the file, folder or database it imported from.
What is the Power Query equivalent of VLOOKUP?
Merge Queries, which joins two queries on a matching column and can bring back every column from the second table, with a choice of join types, rather than one value at a time.
Why did rows disappear after I merged two queries?
The join type used in Merge Queries decides which unmatched rows are kept. The default keeps only matching rows; choose a different join type to keep unmatched rows from one or both tables.
Test yourself
Timed questions on Power Query and Data Model, with an explanation for every answer.
