| Concept | Description |
|---|---|
| Loading and Steps | |
| The Problem Power Query Solves | Manual cleaning leaves no record and must be repeated in full on every new file |
| Sources | From Table/Range, From Workbook or CSV, From Folder, and direct database connections |
| From Folder | Stacks every file in a directory, picking up newly added files on refresh |
| Applied Steps | Each action is recorded as an editable, reorderable step that can be replayed |
| The M Language | The query language underneath, readable in the Advanced Editor and portable between workbooks |
| Transformations | |
| Setting Data Types Explicitly | Inferred types break when a later refresh contains a value the inference did not anticipate |
| Fill Down | Repairs report-style layouts where a category label sits above a block of blank cells |
| Unpivoting | Converts columns holding values of one variable into rows, giving tidy long format |
| Unpivot Other Columns | Pins the columns to keep so new columns are handled automatically on refresh |
| Combining Queries | |
| Append | Stacks rows from tables with matching column names |
| Merge and Join Kinds | Joins columns by key, with left outer behaving like VLOOKUP wrapped in IFERROR |
| Anti Joins as a Quality Check | A left anti join lists exactly the keys present in one table and absent from the other |
| Output and Power Pivot | |
| Load Destinations | Table, PivotTable report, connection only, or the Data Model |
| Never Edit the Output Table | Manual edits to a query's output are silently overwritten by the next refresh |
| The Data Model | Compressed columnar storage holding related tables well beyond the worksheet row limit |
| DAX Measures and SUMX | Measures defined once and reused; SUMX evaluates row by row rather than on column totals |
| Choosing Between Tools | Formulas for one-off work, Power Query for repeated cleaning, R for modelling and reproducibility |
20 Power Query and Power Pivot
Every technique in Data Cleaning and Preparation shares one weakness. The work is done by hand, it leaves no record, and next month’s file means doing all of it again.
Power Query removes that. It records each transformation as a step, keeps the steps in order, and replays the whole sequence against a new file at the press of one button. The source data is never modified. What gets loaded back into the workbook is the result of the recorded steps, and the steps themselves stay visible and editable.
It has shipped with Excel since 2016 under the name Get & Transform, on the Data tab. In Excel 2010 and 2013 it was a separate download. Once a cleaning routine will be run more than twice, this is where it belongs.
20.1 The Shape of the Workflow
20.2 Loading Data
Data, then Get Data, offers a long list of sources. Four cover most work.
From Table/Range takes data already in the workbook. Click inside the range first; Excel converts it to a Table if it is not one already.
From Workbook or From Text/CSV takes an external file. The path is stored, so refreshing re-reads that file. Where the file is replaced monthly under the same name, refresh alone picks up the new contents.
From Folder reads every file in a directory and stacks them. Twelve monthly files with identical layouts become one combined table, and dropping a thirteenth file into the folder adds it on the next refresh. For anyone who currently opens twelve files and copies them one under another, this single feature repays the time spent learning the tool.
From Database connects to SQL Server, MySQL, PostgreSQL, and others, with the query pushed down to the server where possible.
Choosing Transform Data rather than Load opens the editor rather than dumping the data straight into a sheet. Open the editor. Inspecting what arrived before loading it is the point of the tool.
20.3 The Editor and Applied Steps
The Power Query editor shows a preview of the data, a Queries pane on the left, and Applied Steps on the right. Every action taken adds a step to that list.
Steps can be renamed, reordered, edited, or deleted. Clicking a step shows the data as it stood at that point, which makes debugging straightforward in a way manual cleaning never is: when the output is wrong, step through the list until the wrong value appears.
The gear icon beside a step reopens the dialog that created it, so a filter threshold or a replacement value can be changed without rebuilding anything after it.
Each step is a line of M, the query language underneath. View, then Advanced Editor, shows the whole query:
let
Source = Excel.CurrentWorkbook(){[Name="SalesWide"]}[Content],
Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
Typed = Table.TransformColumnTypes(Promoted, {{"District", type text}}),
Unpivoted = Table.UnpivotOtherColumns(Typed, {"District"}, "Quarter", "Volume")
in
UnpivotedWriting M by hand is rarely necessary. Reading it is occasionally useful, since it is the clearest statement of what the query actually does, and it can be copied into another workbook as a complete transformation.
20.4 The Transformations Worth Knowing
| Task | Where |
|---|---|
| Use the first row as headers | Home, Use First Row as Headers |
| Set a column’s data type | Click the type icon in the header, or Transform, Data Type |
| Remove columns | Right-click, Remove, or Home, Choose Columns |
| Filter rows | The dropdown arrow on any column header |
| Trim and clean text | Transform, Format, Trim, and Format, Clean |
| Change case | Transform, Format, lowercase or Capitalize Each Word |
| Split a column | Home, Split Column, By Delimiter |
| Replace values | Right-click a column, Replace Values |
| Remove duplicates | Right-click the header, Remove Duplicates |
| Fill blanks downward | Right-click, Fill, Down |
| Add a calculated column | Add Column, Custom Column |
| Group and aggregate | Home, Group By |
Setting data types deliberately matters more here than anywhere else in Excel. Power Query infers a type for each column when it loads and records that inference as a step. If the first few hundred rows of a yield column happen to be whole numbers, the column may be typed as integer, and a decimal arriving in a later refresh produces an error in every affected row. Set the types explicitly to what the data actually is.
Fill Down is the standard repair for a sheet where a category appears once above a block of rows and the cells beneath are left blank. That layout is common in reports built for reading rather than analysis, and Fill Down converts it back into a proper dataset in one click.
20.4.1 Unpivoting
This is the transformation that justifies learning the tool.
The SalesWide sheet holds one row per district and one column per quarter:
| District | Q1_2026 | Q2_2026 | Q3_2026 | Q4_2026 |
|---|---|---|---|---|
| Guntur | 1240 | 1310 | 980 | 1455 |
| Krishna | 1100 | 1185 | 890 | 1320 |
That layout suits a printed report and blocks almost every analysis. Quarter is a variable, so it belongs in a column of its own rather than being spread across four headers. A PivotTable cannot group by quarter, a chart cannot plot a time series from it, and R will read it as four unrelated numeric columns.
Load it, select the District column, then right-click and choose Unpivot Other Columns. The result:
| District | Quarter | Volume |
|---|---|---|
| Guntur | Q1_2026 | 1240 |
| Guntur | Q2_2026 | 1310 |
| Guntur | Q3_2026 | 980 |
| Guntur | Q4_2026 | 1455 |
| Krishna | Q1_2026 | 1100 |
Rename the two new columns, and the data is now in the long format that PivotTables, charts, and R all expect. This is the same tidy-data structure Wickham (Hadley Wickham, 2014) sets out and the same operation tidyr::pivot_longer() performs.
Unpivot Other Columns rather than Unpivot Columns is the right choice almost always. It pins the columns to keep and unpivots everything else, so a fifth quarter added next year is picked up automatically. Selecting the quarter columns explicitly would exclude any new one.
Pivot Column, on the Transform tab, does the reverse, for the occasions when a report genuinely needs the wide layout at the end.
20.4.2 Append and Merge
Two different operations that get confused because both combine queries.
Append stacks rows. Two files with the same columns, one for Kharif and one for Rabi, become one table with all the rows. Home, Append Queries. Columns are matched by name, so a column called Yield in one file and yield_qt in the other will produce two separate columns with blanks in each, and the fix is to rename before appending.
Merge joins columns by a key, which is the VLOOKUP operation from Lookup and Reference Functions done properly. Home, Merge Queries, select the matching column in each table, and choose the join kind:
| Join kind | Keeps |
|---|---|
| Left Outer | Every row from the first table, matched values where found |
| Right Outer | Every row from the second table |
| Full Outer | Every row from both |
| Inner | Only rows that matched in both |
| Left Anti | Only rows from the first table with no match |
| Right Anti | Only rows from the second with no match |
Left Outer is the everyday join and behaves like VLOOKUP with IFERROR wrapped around it.
The anti joins have no equivalent in ordinary Excel and are worth knowing. Running a Left Anti join from FarmData to CropLookup lists exactly the crop codes present in the data but missing from the lookup table, which is the data quality question that a column of #N/A values only hints at.
After merging, the new column appears collapsed. Click the expand icon in its header and pick which fields to bring through, untick the prefix option, and the join is done.
20.5 Loading the Result
Home, Close & Load To, offers four destinations.
Table writes the result to a worksheet. The usual choice.
PivotTable Report sends it straight into a new PivotTable without landing the rows on a sheet.
Only Create Connection loads nothing into any sheet. Right for intermediate queries that exist to feed another query, and right for data that exceeds the million-row sheet limit.
Add this data to the Data Model loads it into Power Pivot, described below.
Refresh with Data, Refresh All, or Ctrl + Alt + F5. Query Properties allows refresh on file open and at timed intervals.
A query that loads to a Table creates a normal Excel Table, so PivotTables and charts built on it behave as usual and update when the query refreshes. Do not edit that output table by hand. The next refresh overwrites it, and the edit vanishes with no warning. Anything that needs changing belongs in a query step.
20.6 Power Pivot and the Data Model
Power Query gets data in and shapes it. Power Pivot stores several tables together, relates them, and computes over them.
Enable it through File, Options, Add-ins, COM Add-ins, Go, and tick Microsoft Power Pivot for Excel. It is unavailable in some Excel editions, where Power Query still works fine on its own.
Loading several queries to the Data Model and defining relationships between them removes the need for lookups altogether. With FarmData related to CropLookup on CropCode, a PivotTable can use CropName from one table and YieldQtPerHa from the other without a single VLOOKUP. Relationships are drawn in the Diagram View by dragging one key field onto another.
The Data Model also lifts the row ceiling. It holds its tables in compressed columnar storage outside the worksheet grid, so several million rows are workable where a sheet stops at 1,048,576.
Measures are calculations defined once on the model and reused in any PivotTable built from it, written in DAX:
Total Production := SUMX(FarmData, FarmData[YieldQtPerHa] * FarmData[AreaHa])
Average Yield := AVERAGE(FarmData[YieldQtPerHa])
Stressed Share := DIVIDE(
CALCULATE(COUNTROWS(FarmData), FarmData[Status] = "Stressed"),
COUNTROWS(FarmData)
)SUMX is worth noticing, because it addresses exactly the calculated-field problem from PivotTables and PivotCharts. It evaluates the expression row by row and then sums the results, rather than operating on column totals, so the production figure is genuinely the sum of each farm’s production.
DAX is a substantial language of its own and goes well past this topic. For a first course, Power Query is the part that pays off immediately; Power Pivot becomes worth learning when several related tables are involved or the data outgrows the sheet.
20.7 Which Tool for Which Job
| Situation | Tool |
|---|---|
| One-off inspection of a small file | Worksheet formulas and filters |
| Cleaning that repeats every month | Power Query |
| Combining many files with the same layout | Power Query, From Folder |
| Reshaping wide to long | Power Query, Unpivot |
| Joining two tables | Power Query Merge, or XLOOKUP for something quick |
| More than a million rows | Power Query to the Data Model |
| Several related tables in one report | Power Pivot relationships |
| Statistical modelling | R |
| Anything that must be reproducible by someone else | R |
Power Query and an R script solve the same problem, which is that manual work cannot be audited or repeated. Power Query does it with a recorded sequence of clicks, R does it with code. The click-based version is quicker to build and easier to hand to a colleague who does not code; the code version handles branching, functions, testing, and version control. Knowing both, and recognising which the job needs, is most of what practical data preparation amounts to.