Power Query I: Import & Clean

Excel Mastery Track · Purple belt · ~6–8 hours · Microsoft 365 on Windows assumed

Every analyst eventually discovers the ugly truth that the interesting work (the model, the chart, the insight) sits on top of a mountain of janitorial work: files that arrive monthly with the same shape but different names, columns typed as text when they should be numbers, a report exported as a wide crosstab when you need it tall and tidy, three regional exports that must be stacked into one, a product master that has to be joined onto a transactions table. For two decades analysts did this by hand (copy, paste, find-and-replace, a fragile web of helper columns) and redid it from scratch every single month. Power Query is the tool that ends that. It is Excel's built-in ETL engine (Extract, Transform, Load): you perform the cleanup once, by clicking, and Power Query silently records every click as a step. Next month you drop in the new file and press one button (Refresh) and the entire pipeline replays itself against the new data. The cleaning becomes an asset instead of a chore.

This module makes you fluent in the Import & Clean half of Power Query: getting data in from the four sources that cover almost everything (a CSV, an Excel workbook, a whole folder of files, a web page), and then the core transformations that turn raw exports into analysis-ready tables, setting data types, splitting and merging and trimming columns, filling gaps, unpivoting a crosstab into tidy long form, aggregating with Group By, and combining tables two fundamentally different ways: appending (stacking rows) and merging (joining on a key). You will finish with two ideas that separate people who use Power Query from people who trust it: query folding (push work back to the source so it runs fast) and refresh discipline (a query is a living pipeline, not a snapshot, and the moment you forget that it will quietly show you last month's numbers).

The mental model to hold from the first minute: Power Query is not a place you edit data; it is a place you write a recipe. You never touch the source file. You author an ordered list of steps (the Applied Steps) and those steps are re-run, top to bottom, against whatever the source contains at refresh time. Get the recipe right once and it is right forever. Get it subtly wrong, a type set against the wrong locale, an append with mismatched column names, a text filter that doesn't account for case, and it is wrong forever, silently, every month, until someone notices the total is off. This module drills the discipline that keeps the recipe honest.

One warning stated once, up front, because it causes more lost hours than any other single fact about this tool: Power Query is case-sensitive. [State] is not [state]; a filter for "Delhi" does not match "delhi"; a merge key "ACME" will not join to "Acme". The worksheet formula world you came from (XLOOKUP, SUMIFS) is case-insensitive, so this will ambush you. We return to it repeatedly.

Learning objectives

By the end you can:

1. Import data from the four workhorse sources (From Text/CSV, From Workbook, From Folder, and From Web) via their exact ribbon paths, and explain what each returns into the Power Query Editor.
2. Read and edit the Applied Steps pane fluently, understanding that every step is one recorded, re-runnable transformation, and that the query is the ordered list of them, not the data preview you see.
3. State why the first thing a query should do is set data types, and set them correctly (including locale-aware typing) so that later steps and the final load behave predictably.
4. Perform the core column transforms (Split, Merge, Trim, Clean, and Fill Down/Up) from their ribbon locations, and say what problem each one solves.
5. Unpivot a wide "attribute-across-the-top" crosstab into tidy long form (and Pivot back), and explain why tidy data is the shape every downstream tool wants.
6. Aggregate inside the query with Group By, producing sums, counts, and other rollups without a single worksheet formula.
7. Distinguish Append (stacking same-shaped tables into more rows) from Merge (joining tables side-by-side on a key), choose the correct join kind (left outer, inner, and the rest), and know that append requires identically named columns.
8. Explain query folding (pushing transformation work back to the source) why it matters for speed, and how to check for it with View Native Query.
9. Choose the right Close & Load To destination (worksheet table, connection-only, or the Data Model) and practise refresh discipline so your outputs never silently lag the source.
10. Diagnose the classic Power Query failures, untyped data and locale chaos, case-sensitivity bites, append column-name mismatches, wrong join kinds, loading a huge query to a sheet, and editing a source without refreshing.

Prerequisites & connections

Builds on. EX0.01 (Tables & structured references, Ctrl+T). Power Query loves a real Table as a source and hands back real Tables, and the "one row per observation" habit starts there. EX1.01/EX1.02 cover the aggregation and lookup families. Group By is the query-side cousin of SUMIFS, and Merge is the query-side cousin of XLOOKUP/INDEX+MATCH. Knowing the worksheet versions makes the query versions click instantly. EX2.01 (Data Tools & Integrity) brings data types and validation, plus the same "clean at the boundary" instinct. You don't need any coding. Power Query is driven by ribbon and mouse, and you'll read the M language underneath long before you ever write it.

Feeds forward. EX4.02 (Power Query II) takes the recipe idea further, into parameters, custom columns in M, conditional and fuzzy merges, and turning transformations into reusable functions. EX5.01 and EX5.02 wire a refreshed query into the Data Model and PivotTables, so a single refresh updates an entire dashboard. On the analyst track, this is the plumbing under everything. M2.05's peer-benchmark template ingests raw exports and appends new quarters. M3.08's three-statement model pulls actuals from a monthly folder. Every "living" report that updates with one click (EX7.01) has a Power Query pipeline underneath. The tidy-data move you learn here, unpivot, is the most valuable reshape in analytics, and you'll reach for it in nearly every real project.

Core teaching content

Group By: aggregate in the query

Group By is SUMIFS/COUNTIFS done inside the query. It collapses many rows into one row per group, carrying an aggregate. Once your data is tidy, a rollup is one dialog away, with no worksheet formula and no spill.

Transform ▸ Group By (also Home ▸ Group By) opens a dialog. Choose Basic to group by a single column, or Advanced to group by several. Pick the grouping column or columns, e.g. State. Then define one or more aggregations. Each one needs a New column name such as Total Revenue, an Operation, and the Column to aggregate, here Revenue. The Operation can be Sum, Count Rows, Average, Min, Max, Median, Count Distinct Rows, or "All Rows" to nest the raw rows. Click Add aggregation for more, e.g. Sum of Units as well, and Count Rows for order count. You get one row per state, with the totals.

Professionals take two things to heart here. First, Group By is destructive. The detail rows are gone from that query's output, unless you use the "All Rows" operation to keep them nested. So if you still need the detail, group in a branch. Use right-click the query ▸ Reference to spin off a summary query and leave the detailed one intact. Second, Group By can fold, which comes up below. Against a database, doing the aggregation in the query can be dramatically faster than pulling a million detail rows into Excel and summing them there. You pull back four rows instead of a million.

Common mistakes & how experts think differently

1. Not setting types first, and the locale chaos that follows. Beginners transform first and type last, or never. So an intermediate split produces text, a date column sorts alphabetically, and Group By refuses to sum. Worse, they click "Date" on a dd/mm/yyyy file while sitting at a US-locale machine. Then 13/04/2025 errors with no warning, while 03/04/2025 becomes the wrong date with no error at all. That second one is the nasty one. The expert promotes headers, then immediately sets every column's type, using Change Type ▸ Using Locale… for any date or decimal whose source locale differs from the machine's. Only then do they transform. Types are the foundation, so they go in first.
2. Forgetting Power Query is case-sensitive. The worksheet world is case-insensitive, so SUMIFS treats "Delhi" and "delhi" as equal. People carry that assumption into Power Query, where it's false. Think of a Filtered Rows step for "delhi", a merge key "ACME" against "Acme", or an append column revenue against Revenue. Each one fails to match, and none of them tells you. The expert normalises case on purpose before matching, with Transform ▸ Format ▸ lowercase/UPPERCASE on keys or Text.Lower in a step. Until case and stray spaces are handled, they never assume two strings that "look the same" really are the same.
3. Appending tables whose column names differ, even by a single character. Append aligns columns by name, case-sensitively. Feed it Sales Amt and SalesAmt, or Revenue and revenue, and it keeps both columns, nulling each for the rows that lacked it. The result looks like data but is half-empty. The expert standardises headers across every input before appending, renaming them to a single canonical set with matching case. Then they sanity-check the appended row count against the sum of the inputs.
4. Picking the wrong join kind on a Merge. A Left Outer where Inner was meant keeps unmatched rows with null attributes, and those then poison downstream sums. An Inner where Left Outer was meant deletes the unmatched rows, and your total drops with no warning. The expert states the intent in words first. Is it "keep every order (Left Outer)", "keep only orders with a valid product (Inner)" or "show me the orphans (Left Anti)"? They pick the join to match, then reconcile the output row count against the input.
5. Loading a huge query to a worksheet instead of connection-only. Dumping millions of rows onto a sheet is slow and bloats the file. It can hit the ~1,048,576-row limit too, and it's usually pointless, since nobody reads a million rows. The expert loads to Only Create Connection (+ Data Model) and puts on a sheet only the summarised rows a human will actually look at. Group By and the model do the work off-grid.
6. Editing the source but not refreshing. The output is a snapshot from the last refresh. Change the source and the sheet lies until you press Refresh All (Ctrl+Alt+F5). Beginners trust the stale number. The expert treats refresh as a ritual. They refresh after any source change and set Refresh on open for live workbooks. And they reconcile a control total after every refresh, so a broken pipeline announces itself.
7. Doing folding-breaking work before folding-friendly work (on database sources). Add a fancy custom column first and you break query folding. The filter and Group By that follow then run in Excel over the full dataset, when they could have been pushed to the server. The expert front-loads the row-reducing, foldable steps (filter, remove columns, Group By) and checks View Native Query to confirm they folded. Only then do they add the steps that break folding, so the source ships back as little data as possible.

All seven come back to one habit. A query is a recipe that will run unattended, forever, against data you haven't seen yet. The beginner cleans what's in front of them today. The expert writes steps that stay correct next month: typed first, case-normalised, named to match, joined with intent, refreshed on ritual.

Worked examples

Worked example 1: India monthly sales: import a folder and append, then Group By state (India)

A regional business drops one CSV per month into a shared folder: jan-2025.csv, feb-2025.csv, mar-2025.csv, each with the same columns. Your job is to import the whole folder and append the files into one table. Set types, then Group By state to total revenue (₹). After that, verify the appended row count and one state's total. Python recomputed every figure in the example, and they're all shown.

Here are the three source files. Each has the columns OrderID, OrderDate, State, Units, Revenue, with revenue in ₹, and by construction every line is Units × ₹4,500:

jan-2025.csv                                  feb-2025.csv                                  mar-2025.csv
OrderID OrderDate  State        Units Revenue  OrderID OrderDate  State       Units Revenue  OrderID OrderDate  State        Units Revenue
1001    2025-01-05 Maharashtra  10    45000    1005    2025-02-03 Tamil Nadu  12    54000    1010    2025-03-04 Maharashtra  11    49500
1002    2025-01-11 Karnataka    6     27000    1006    2025-02-09 Karnataka   7     31500    1011    2025-03-12 Tamil Nadu   6     27000
1003    2025-01-19 Delhi        8     36000    1007    2025-02-16 Maharashtra 9     40500    1012    2025-03-20 Delhi        10    45000
1004    2025-01-27 Maharashtra  5     22500    1008    2025-02-22 Delhi       4     18000    1013    2025-03-29 Karnataka    8     36000
                                               1009    2025-02-28 Karnataka   3     13500
(4 data rows)                                  (5 data rows)                                 (4 data rows)

Step 1: connect to the folder. Data ▸ Get Data ▸ From File ▸ From Folder ▸ browse to the folder (e.g. C:\Sales\2025\) ▸ Open. Power Query shows a preview listing the three files with their metadata (Name, Extension, Date modified, Folder Path).

Step 2: combine, which is the append. On that preview, click the Combine button's dropdown ▸ Combine & Transform Data. In the Combine Files dialog, choose the Sample File (First file) and confirm the CSV settings (delimiter = comma, encoding). Click OK. Power Query builds a small set of helper queries automatically. You'll see a Sample File, a Parameter, a Transform File function, and a Transform Sample File. Then it produces a combined query whose rows are every data row from all three files stacked, with an extra Source.Name column showing which file each row came from. Headers are promoted once and aren't repeated per file. So the folder-combine really is an append. For files you've already imported, the manual equivalent is Home ▸ Append Queries.

Step 3: set types first. In the combined query, set types before anything else. Set OrderID = Whole Number, State = Text, Units = Whole Number and Revenue = Currency (or Decimal). For the date, use right-click OrderDate ▸ Change Type ▸ Using Locale… ▸ Data Type = Date, Locale = English (India). That way dd-mm style text, if any turned up, would read correctly whatever machine does the refresh. Then run Transform ▸ Format ▸ Trim and ▸ Clean on State, so "Delhi " can't split off from "Delhi" when we group.

Step 4: Group By state. Transform ▸ Group By ▸ Basic ▸ group by State ▸ New column name Total Revenue, Operation Sum, Column Revenue. Click Add aggregation ▸ Orders, Operation Count Rows. Click OK.

Step 5, the numbers (Python-verified). The appended table has 13 data rows (4 + 5 + 4), and Group By gives one row per state:

Check Maharashtra longhand. Its four orders are 45,000 + 22,500 (Jan) + 40,500 (Feb) + 49,500 (Mar) = ₹1,57,500. Now the grand total: 1,57,500 + 1,08,000 + 99,000 + 81,000 = ₹4,45,500. The order count 4 + 4 + 3 + 2 = 13 reconciles with the appended row count. That's the control check, and it proves the append lost nothing.

Step 6: Close & Load and refresh. Use Home ▸ Close & Load To… ▸ Only Create Connection for the big combined query. Load the small 4-row Group By result as a Table on a sheet, since that's the output a human reads. Next month, drop apr-2025.csv into the same folder and press Ctrl+Alt+F5. The folder re-combines and April's rows append. Then the Group By re-totals itself. You don't rebuild anything or edit a formula. That one-button monthly refresh is why you do the job in Power Query at all.

Worked example 2: US product sales: unpivot a wide crosstab, then Group By product (US)

This time a US sales export arrives as a wide crosstab, with products down the side and months across the top. Each cell holds one sales figure ($). You'll unpivot it into a tidy Product / Month / Sales table, then Group By product to total each product across the quarter-plus. Finally, check the unpivoted row count and one product's total. Python recomputed all the figures.

The source crosstab is a Table on a worksheet, with sales in $:

Product   Jan     Feb     Mar     Apr
Alpha     1200    1350    1100    1400
Bravo      800     900     950     850
Charlie   1500    1600    1450    1550
Delta      600     650     700     620

Step 1: connect. Select the range and press Ctrl+T to make it a real Table, if it isn't one already. Then use Data ▸ Get Data ▸ From Other Sources ▸ From Table/Range, or the shortcut Data ▸ From Table/Range. The editor opens with the four month columns and one product column.

Step 2: set types first. Set Product = Text. Leave the month columns for now, because they'll become one Sales column that we type in Step 4. Run Transform ▸ Format ▸ Trim/Clean on Product.

Step 3: unpivot. Click the Product column header to select it, then right-click it ▸ Unpivot Other Columns. Transform ▸ Unpivot Columns ▸ Unpivot Other Columns does the same. Power Query melts Jan, Feb, Mar, Apr into two columns, Attribute for the month name and Value for the figure. Because you chose Unpivot Other Columns on the Product identifier, a future May column gets swept in automatically on refresh.

Step 4: tidy up. Rename Attribute → Month and Value → Sales by double-clicking each header. Set Month = Text and Sales = Currency. You now have tidy long data, 4 products × 4 months = 16 rows, one row per product-month.

Here's the unpivoted table, with all 16 rows shown so you can audit the count:

Product  Month  Sales      Product  Month  Sales
Alpha    Jan    1200       Charlie  Jan    1500
Alpha    Feb    1350       Charlie  Feb    1600
Alpha    Mar    1100       Charlie  Mar    1450
Alpha    Apr    1400       Charlie  Apr    1550
Bravo    Jan     800       Delta    Jan     600
Bravo    Feb     900       Delta    Feb     650
Bravo    Mar     950       Delta    Mar     700
Bravo    Apr     850       Delta    Apr     620

Step 5: Group By product. Transform ▸ Group By ▸ Basic ▸ group by Product ▸ New column name Total Sales, Operation Sum, Column Sales ▸ OK. Python verified the four totals:

Check Charlie longhand: 1,500 + 1,600 + 1,450 + 1,550 = $6,100. Then the grand total: 5,050 + 3,500 + 6,100 + 2,570 = $17,220. There's a second reconciliation too, and it proves the unpivot lost nothing. The grand total equals the sum of every cell in the original crosstab, 16 cells summing to $17,220, so the reshape kept every dollar.

Step 6, read it. You didn't retype a single figure. The unpivot and Group By fetched and rolled up the source. Replace the export next month (or add a May column) and one refresh re-melts and re-totals it. The tidy table is also chart-ready and PivotTable-ready now, in a way the crosstab never was. Every analyst who has been burned by a crosstab learns to "unpivot on the way in" for that reason.

Practice set

Work each problem before you read its solution. Assume M365 on Windows, and give exact ribbon paths wherever a path is asked for.

P1 (guided). Get Data entry points. Name the exact ribbon path to import (a) a single .csv, (b) one sheet from another workbook, (c) every file in a folder, and (d) a table from a web page. Why click Transform Data and not Load for a CSV?

Solution. (a) Data ▸ Get Data ▸ From File ▸ From Text/CSV. (b) Data ▸ Get Data ▸ From File ▸ From Workbook, then pick the sheet/Table in the Navigator. (c) Data ▸ Get Data ▸ From File ▸ From Folder. (d) Data ▸ Get Data ▸ From Other Sources ▸ From Web. Click Transform Data so the data opens in the Power Query Editor, where you can set types and clean before anything reaches a sheet. Load dumps the raw import straight onto the grid and skips the recipe you want to build.

P2 (guided). Applied Steps. A colleague says "the data is wrong" and points at the preview. Explain what the preview actually shows, where the real logic lives, and how to see the data as it was three steps ago.

Solution. The preview in the centre is a live rendering of the recipe applied to a sample. It's neither the data itself nor the query. The real logic is the ordered Applied Steps list in the Query Settings pane on the right, and the query is that list. To see the data three steps ago, click that earlier step in Applied Steps. The preview re-renders as of that step, so the steps above it have run and the ones below haven't. Fixing "wrong data" means finding and editing the step that caused it. The preview is only where you noticed.

P3. Set types first + locale. A file exported in India has a date column written 05/03/2025 meaning 5 March 2025, but your laptop is set to US locale. You click the calendar icon and choose "Date." What goes wrong, and what's the correct action?

Solution. With US locale, Power Query reads 05/03/2025 as mm/dd, which gives 3 May 2025. That's the wrong date, and you get no error. A value that can't be a date at all, like 13/04, would error instead. To fix it, right-click the column ▸ Change Type ▸ Using Locale… ▸ Data Type Date, Locale English (India) (dd/mm/yyyy). The resulting Changed Type with Locale step reads the source's format correctly on any machine. So set types first, and use Using Locale whenever the source's date or number format differs from the refreshing machine's.

P4. Split and merge. A column Location holds "Pune, Maharashtra". (a) Give the path to split it into City and State on the comma. (b) Later you must build a key State|Product. Give the path, and say why a separator is used.

Solution. (a) Select Location ▸ Transform ▸ Split Column ▸ By Delimiter ▸ delimiter Comma ▸ split at Each occurrence (or left-most) ▸ OK. Then Trim the new State column. (b) Select the State then Product columns (in that order) ▸ Transform ▸ Merge Columns ▸ Separator Custom | ▸ name it Key. The | separator prevents false collisions. Without it, "12"+"3" and "1"+"23" both become "123", and the separator keeps distinct keys distinct.

P5. Trim, Clean, Fill. (a) A State column won't group correctly. "Delhi" appears as two groups. Give the two ribbon operations that most likely fix it and what each removes. (b) A report has Region written once above eight blank rows per region. Give the path to repair it.

Solution. (a) Transform ▸ Format ▸ Trim removes leading and trailing spaces, so "Delhi " becomes "Delhi". Transform ▸ Format ▸ Clean removes non-printing control characters like tabs and line breaks. One or both usually explains a value that "looks identical" but groups separately. (b) Select Region ▸ Transform ▸ Fill ▸ Down, or right-click ▸ Fill ▸ Down. It copies each non-null value down into the nulls beneath it until the next non-null, which puts the label back on every row.

P6. Unpivot row count (numeric). A crosstab has products down the side and 12 month columns across the top, with 6 products. After selecting the product column and choosing Unpivot Other Columns, how many rows does the tidy result have, and what are the two new column names before you rename them?

Solution. Unpivot produces one row per product-month, so 6 × 12 = 72 rows. The two new columns are Attribute, which holds the month header, and Value, which holds the figure. You rename them to Month and, say, Sales. Because you chose Unpivot Other Columns and selected the product identifier, a 13th month added later gets swept in automatically on refresh.

P7. Append requirement (numeric + rule). You append three regional CSVs of 220, 240, and 260 data rows. (a) How many rows should the appended table have? (b) One file's revenue header is revenue while the others use Revenue. What does append do, and how do you prevent it?

Solution. (a) Append stacks rows, so the result has 220 + 240 + 260 = 720 rows. (b) Append matches columns by name, case-sensitively, so revenue and Revenue are treated as two different columns. The result keeps both, with null in Revenue for the odd file's rows and null in revenue for the others. Prevent it by renaming the headers on every input to one canonical set, with matching case, before appending. Then reconcile the appended row count to 720 as a control check.

P8. Choose the join kind. You merge an Orders table (5 rows, keys C001, C002, C001, C009, C003) with a Customers master (keys C001, C002, C003, C004). Give the resulting row count and behaviour for (a) Left Outer, (b) Inner, (c) Left Anti.

Solution. (a) Left Outer keeps all 5 order rows. C009 has no master match, so its expanded customer columns are null. (b) Inner keeps only rows matching in both, so C009 is dropped, leaving 4 rows. (c) Left Anti keeps only orders with no match. That's just the one C009 row, so 1 row, and it's perfect for finding orphans. Pick by intent. Use Left Outer when you want every order and Inner when you want only the valid ones. For the orphans, use Left Anti.

P9. Group By (numeric). After appending, a tidy table has these (Region, Revenue) rows: (North, 12000), (South, 15000), (North, 8000), (West, 9000), (South, 5000). Give the Transform ▸ Group By settings to total revenue per region, and the resulting totals and grand total.

Solution. Transform ▸ Group By ▸ Basic ▸ group by Region ▸ New column Total Revenue, Operation Sum, Column Revenue. The totals are North = 12,000 + 8,000 = 20,000, South = 15,000 + 5,000 = 20,000 and West = 9,000. Grand total = 20,000 + 20,000 + 9,000 = 49,000. That reconciles with summing all five rows (12k+15k+8k+9k+5k), which is your control check.

P10. Case-sensitivity. You filter a tidy table to Delhi with a step that keeps rows where State = "delhi", and get zero rows even though "Delhi" is clearly present. Explain, and give two fixes.

Solution. Power Query is case-sensitive, and "delhi" ≠ "Delhi", so the filter matches nothing. Fix 1: filter for the case that's actually present ("Delhi"). Fix 2 is the sturdier one. Normalise case first. Select State ▸ Transform ▸ Format ▸ lowercase, which makes every value "delhi", then filter for "delhi". Now case can never bite. Make it a general habit to case-normalise, and Trim/Clean, any column you filter, group, merge or append on.

P11. Connection-only vs load to sheet. You build a query returning ~1.8 million rows that only feeds a PivotTable. A teammate loads it as a worksheet Table and the workbook becomes huge and slow. What should they have chosen, and why?

Solution. Home ▸ Close & Load To… ▸ Only Create Connection, and tick "Add this data to the Data Model". Loading 1.8M rows to a sheet is slow, bloats the file and approaches the ~1,048,576-row worksheet limit. And nobody reads 1.8M rows anyway. Connection-only keeps the data in memory or in the model and off the grid, and the PivotTable consumes it efficiently from there. Put on a sheet only the summarised output a human reads.

P12. Refresh habits. You correct three prices in the source CSV and re-open the workbook, but the totals are unchanged. Why, and what's the fix? Name one setting that prevents a recurrence too.

Solution. The worksheet output is a snapshot from the last refresh. The query hasn't re-run, so it still shows the old prices. To fix it, press Ctrl+Alt+F5 (or Data ▸ Refresh All), which replays the recipe against the edited source. To stop it happening again, right-click the query in Queries & Connections ▸ Properties ▸ tick "Refresh data when opening the file", so opening the workbook refreshes it automatically. A query is a live pipeline, and what it leaves on the sheet is a frozen result. Make refresh a ritual, and let a reconciled control total tell you when you forgot.

P13. Query folding. Against a SQL database you add a complex custom column first, then a filter, then Group By, and the refresh is slow. Explain in folding terms and give the reordering fix and the check.

Solution. Query folding translates steps into native SQL that's pushed to the server. Folding breaks at the first step the source can't express, which here is the complex custom column. So the later filter and Group By run in Excel over the full dataset, when they should have run on the server. Reorder so the foldable, row-reducing steps come first: filter, remove unused columns, Group By, then the custom column. To check, right-click a step ▸ View Native Query. If it shows SQL, the step folded, along with all the ones above it. Put the folding-breaker as late as possible, so the server ships back the least data.

Applied mini-project

Your project is to build a self-refreshing regional sales pipeline. Assemble one Power Query pipeline that ingests a folder of monthly sales files, cleans and reshapes them, and enriches them with a product master. It should output a small set of analysis-ready summaries. Then prove it survives a new month with a single refresh.

First, set up the inputs, a folder plus one master workbook:

1. A folder (e.g. \Sales\2025\) holding at least three monthly CSVs of identical shape, with columns OrderID, OrderDate, State, ProductCode, Units, Revenue. Make one file deliberately messy. Give it a trailing space in a State value, a date in the local dd/mm/yyyy format, and one header cased differently (revenue) so you have to standardise it.
2. A ProductMaster workbook Table (ProductCode, ProductName, Category) that covers most of the product codes used in the CSVs, but deliberately not all of them, so an unmatched code exists to test join behaviour.

Then build the pipeline. Every step gets recorded, and nothing is typed twice:

1. Import and append the folder with Data ▸ Get Data ▸ From File ▸ From Folder ▸ Combine & Transform Data. Confirm the appended row count equals the sum of the files' data rows. That's your first control check.
2. Set types first, using Change Type ▸ Using Locale… for OrderDate. Then Trim/Clean and case-normalise State and ProductCode. Standardise the odd revenue header to Revenue before the combine relies on it.
3. Merge in the product master with Home ▸ Merge Queries on ProductCode, using Left Outer to keep every order, and expand ProductName and Category. Then run a Left Anti merge as a separate query to list orders whose ProductCode has no master row. Hand that list to whoever owns the master, so they can fix it.
4. Build two Group By summary queries with right-click ▸ Reference off the cleaned table. One groups by State (Sum of Revenue, Count of Orders) and the other by Category (Sum of Revenue).
5. As a separate unpivot drill, take a wide product × month budget crosstab and use Unpivot Other Columns to turn it into tidy Product/Month/Budget, ready to compare against actuals.
6. Load deliberately with Close & Load To…. Send the big appended and merged query to Only Create Connection (+ Data Model), and put the small Group By summaries on a sheet as Tables. Then add next month's CSV to the folder and press Ctrl+Alt+F5. Every summary should re-total with zero manual edits, so confirm that it does.

Rubric (self-score; 0 = missing/wrong, 1 = partial, 2 = correct and defended):

Pass: ≥ 8/10, with no zero on any criterion. Keep the workbook. This import-clean-enrich-summarise pipeline is the template you'll clone for every recurring report you ever own.

Lab: open the Power Query import & clean workbook lab in the Practice ▸ Excel tab and pass its checks.

Reading & resources

Function and feature references (reach for these first):

• Microsoft Learn, the official Power Query documentation (search "Power Query documentation Microsoft Learn"): authoritative pages on Get Data, data types and locale, unpivot, Group By, append vs merge, join kinds, and query folding. [Free] [Beginner]
• Microsoft Support. "Combine files (Binaries) in Power Query" and "Merge queries / Append queries" walkthroughs: the exact dialogs, screenshot by screenshot. [Free] [Beginner]

Deeper practice and pattern libraries:

• ExcelIsFun (Mike Girvin, YouTube). Long, worked Power Query playlists that build real pipelines end to end, and the best free moving-picture treatment of import-and-clean. [Free] [Beginner]
• Chandoo (chandoo.org). Power Query tutorials with downloadable practice files, strong on folder-combine and unpivot. [Free] [Beginner]
• Ken Puls & Miguel Escobar, Master Your Data with Excel and Power BI, the definitive book on Power Query for Excel. Its folding and refresh chapters go well beyond what's covered here. [Paid] [Intermediate]

For the tidy-data mindset (the unpivot section, expanded):

• Hadley Wickham, "Tidy Data" (the paper, freely available). It's the formal statement of why one-variable-per-column, one-observation-per-row is the shape every tool wants, and unpivot is how you get there. [Free] [Intermediate]

Bridge forward: parameters, custom columns written in M, and reusable query functions are the whole subject of EX4.02. Once your folder pipeline refreshes cleanly, skim its intro to see how a query becomes a function you call.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand, that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

AI-Augment this skill

{
  "skill": "Power Query as a reproducible pipeline — the Applied Steps recipe, the append name-matching rule, and the honest boundary between Power Query and a Python/pandas pipeline",
  "use": "Power Query's biggest professional risk is not a wrong click, it is a silent one: an upstream file that renamed a column, changed a date format, or dropped a sheet, which the recipe absorbs without complaint. Use an assistant to draft the ASSERTION step a human keeps forgetting to add — a row-count or column-name check that deliberately errors the refresh when the source has drifted, rather than quietly loading a wrong number. Use it too for translating a Power Query recipe into the equivalent pandas pipeline when a pipeline has outgrown what Power Query does well, so you can compare the two side by side before committing to a rebuild.",
  "tools": ["Coding assistants — Claude, ChatGPT, or a notebook's inline assistant to draft the schema-assertion M code and the pandas equivalent of a query", "Chat assistants — for explaining an unfamiliar Advanced-Editor M expression before you edit it", "Document/PDF AI — for confirming a source system's documented column names and types before you build the per-file cleaning function against them"],
  "prompt": "Here is my M query's Applied Steps (or the Advanced Editor text): <PASTE>. Add a step, right after the source loads, that asserts the expected column names are all present and the row count is within a plausible range — and have it raise a clear error (not a silent null) if either check fails. Then tell me, in one paragraph, at what row count or transformation complexity you would recommend I move this specific pipeline to pandas instead, and why.",
  "verify": "Deliberately break the assertion once — rename a source column or truncate a test file — and confirm the query now fails LOUDLY on refresh instead of loading a quietly wrong number; an assertion that does not actually fire when the data is wrong is decoration, not a safeguard.",
  "diy": "The gate is unaided: you build the folder-import-and-append pipeline, state from memory that Append matches columns by name case-sensitively, and explain in one sentence why a stale pivot or query total does not mean the file is broken — with no tool in the room."
}

Modern Data Analysis

By hand first. You can already build the folder-import-and-append pipeline and set types before transforming, and you know that Append matches by name, case-sensitively. Code won't give you a faster click path, because Power Query's UI is already fast. What it gives you is a query that becomes text. That's a dependency graph you can read, diff and assert against, where before you had a black box that either "worked" or failed without telling you.

Today's workflow. Treat a refresh as a re-execution. It re-runs the recipe and checks nothing. Every time Ctrl+Alt+F5 runs, the recipe re-applies to whatever the source currently is, and it has no memory of what the source used to look like. So the professional habit is to make the pipeline assert its own assumptions, such as an expected column list and a plausible row-count range. Then if something changes upstream without warning (a renamed column, a reformatted date, a dropped file), the refresh fails loudly. Without the assertion, it would load a confidently wrong total. Power Query has a boundary, too. It's the right tool while the pipeline is a handful of files and UI-expressible reshapes. Once it needs branching logic, external APIs, statistical transforms, or version-controlled review by a team, the same recipe is better expressed as a pandas script that a code reviewer (human or AI) can actually read.

Tools & sources (IN + US). Use pandas for the equivalent pd.concat/merge pipeline and the schema-drift simulation below. For India, take the same shape of state-wise monthly sales folder the first worked example builds, the kind a regional distributor's branch offices actually email in. For the US, use a comparable multi-file vendor or regional export, the shape the second worked example's product crosstab has before it's unpivoted.

# recomputed in-session — R3
jan = {"Maharashtra": 67500, "Karnataka": 27000, "Delhi": 36000, "Tamil Nadu": 0}
feb = {"Maharashtra": 40500, "Karnataka": 45000, "Delhi": 18000, "Tamil Nadu": 54000}
mar = {"Maharashtra": 49500, "Karnataka": 36000, "Delhi": 45000, "Tamil Nadu": 27000}
states = jan.keys()
correct = {s: jan[s]+feb[s]+mar[s] for s in states}
print(sum(correct.values()))  # 445,500 -- matches the module's own WE1 grand total exactly

Verify. The recomputed grand total is ₹4,45,500 and Maharashtra's total recomputes to ₹1,57,500. Both match the first worked example's figures, which confirms that the per-state, per-month figures used below are that example's real data and not a fresh dataset.

Quantitative lens

A case-mismatched column header is no cosmetic slip. It causes an understatement you can compute, and nothing warns you. The flashcards already state the rule that Append matches columns by name, case-sensitively. Now apply it to the first worked example's real three-file pipeline. Suppose March's file had shipped with the header state instead of State, a one-character drift that's easy to miss. Append wouldn't merge March's state values into the same State column as January and February. It would create a second column, leaving State null for March's four rows.

Group By State would then total only January and February's nine rows. Maharashtra reports ₹1,08,000 instead of the true ₹1,57,500, understated by 31.43%. Karnataka shows ₹72,000 instead of ₹1,08,000 (33.33%), Delhi ₹54,000 instead of ₹99,000 (45.45%) and Tamil Nadu ₹54,000 instead of ₹81,000 (33.33%). Add up the four named State groups and the visible grand total is ₹2,88,000 against the true ₹4,45,500. That's an understatement of ₹1,57,500, 35.35%. March's revenue is still there, untouched, in an easy-to-overlook second column or a blank-keyed group. You won't find it in an error message.

Query folding is an argument about data volume. It isn't a speed benchmark, and nothing here can measure one. The mastery check already establishes that CSV and folder sources never fold, because there's no server to push work to. Folding only matters once a database sits behind the query. As an illustration, suppose the same 13-row, Group-By-state result were built against a SQL source of 2,000,000 historical order rows instead of three CSVs. Folding pushes the filter and GROUP BY to the server, and only the 13 result rows cross into Excel. That's a 153,846× reduction in rows transmitted. The ratio is a property of the row counts stated and isn't a timing measurement. What you can fairly claim is how much less data moves. How many seconds a refresh takes is a separate question.

A word on limits. The case-drift scenario is a deliberate "what if" applied to the first worked example's real numbers. Nothing in that example's own three files carries this drift. The folding ratio illustrates the mechanism at a stated hypothetical scale (2,000,000 source rows), which was chosen because it gives a clean, memorable number. It doesn't measure any specific database's performance.

Where this goes next: galaxy cross-links

• G2 · CS2.01 Command Line, Environments & Reproducible Projects. The reproducibility argument made here about a query as a re-executable recipe is that module's subject, applied to a spreadsheet pipeline instead of a script.
• G2 · CS2.03 Databases & SQL for Analysts. Query folding only has force once a real database sits behind the query, and that's what that module builds toward.
• G2 · DA1.02 pandas I: Data Wrangling. It's the natural next step once a Power Query pipeline outgrows the UI, and the direct code-native equivalent of every append and group-by you've done here.

Teach it back & journal

Feynman prompt. Explain Power Query to someone who has only ever cleaned data by hand, using the metaphor of a recipe card versus a cooked meal. When you clean a file by hand you cook one meal and eat it. Next month you cook again from scratch. Power Query has you write the recipe once, with each instruction (chop the onions, set the oven) as an Applied Step. Every month after that you just hand it fresh ingredients, a new file in the folder, and press Refresh to cook the identical dish. Then explain the one twist that catches everyone. The recipe is followed literally and case-by-letter. If it says "add the jar labelled Delhi", it'll walk right past a jar labelled delhi. And if you swap an ingredient in the pantry but never press Refresh, you're still served last week's meal. Only in your last two sentences may you use the words Applied Steps, case-sensitive and refresh. Attach each one to the moment in the story it describes.

Journal prompt. Find one recurring task you currently redo by hand. It might be a monthly export you copy-paste-and-clean, a set of regional files you stitch together, or a crosstab you retype tall. Write down (1) every manual step you take, in order, because that list is the Power Query recipe you'd build. Note (2) the one reshape that would help most, which is almost always an unpivot or a folder-append. And note (3) the single control total you'd reconcile after each refresh to know the pipeline hadn't broken behind your back. Finally, write the one sentence you want to remember forever about why a query is a recipe and never a snapshot, and why "I edited the source" and "the report is correct" are two different claims until you've pressed Refresh.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.