Power Query II: M & Pipelines

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

In Power Query I you learned to clean data by clicking: promote headers, change types, split a column, filter rows, unpivot a crosstab. Every one of those clicks was writing a line of code behind the scenes in a language called M, and the Applied Steps pane you watched grow was nothing more than that code, listed one step at a time. This module opens the hood. Once you can read and write M directly, you stop being a passenger who can only take the turns the ribbon offers. You become a driver who can go anywhere: parameterise a transform, apply it to a hundred files at once, trap and quarantine bad rows instead of letting them poison a total, and build a pipeline that keeps working next month when the source file changes shape.

The through-line of this module is a single shift in how you see a query. A beginner sees a list of things Excel did. A professional sees a program: a sequence of named values, each computed from the one before, ending in the value the query returns. That's all the structure M has (let a few names equal a few expressions, in return one of them), and everything else you'll meet is a variation on it. The custom function you will write is a named value that happens to be a function. The folder pipeline is that function invoked once per file and the results stacked. Error handling is a named value wrapped in try. Robustness is choosing expressions that survive a source that drifts. Master the let … in mental model and the rest falls into place.

One promise about pace: you do not need to memorise M's several hundred library functions, and this module will not drill them. You need the grammar, how steps chain, what each and _ mean, the three container types, how a function is defined and invoked, plus a handful of workhorse functions (Table.SelectRows, Table.AddColumn, Table.TransformColumns, Folder.Files, Table.ExpandTableColumn, try … otherwise). With the grammar in hand you can read any generated step, look up a function you don't recognise, and write the few lines the GUI cannot generate for you. That reading-and-editing fluency is what separates an analyst who owns their pipelines from one who is stuck with whatever the buttons produce. Encyclopaedic recall doesn't.

One version note, stated once: everything here assumes Microsoft 365. The Power Query engine (Get & Transform) ships in every modern Excel and in Power BI, and the M code you write here is portable between them, a genuine transferable skill, not an Excel-only trick. Menu paths are given for the Windows ribbon; the Mac editor is close but not identical.

Learning objectives

By the end you can:

1. Read any Power Query query as an M program: explain the let … in structure, and articulate the step-is-a-variable model in which each step is a named value computed from an earlier one.
2. Open the Advanced Editor, identify how a step references the previous one by name (including the #"Name with spaces" form), and predict what breaks when a step is renamed.
3. Explain each as sugar for (_) => …, read a bare [Column] as _[Column], and diagnose each-scope confusion in nested contexts.
4. Name and use M's three container types (the list { }, the record [ ], and the table) and access items in each with the correct bracket and index/field syntax.
5. Write and name a custom function with typed parameters ((x as number) as number => …) and invoke it over a column with Table.AddColumn(…, each f([Col])).
6. Build a folder-ingestion pipeline end to end: point at a folder, invoke a per-file transform function on every file's binary, expand the results into one table, and derive metadata (a Month label) from each file name.
7. Trap and route errors with try … otherwise, read the record try returns (HasError, Value, Error), and choose deliberately between Remove Errors, Keep Errors, and quarantining.
8. Make a pipeline robust to schema drift (a source that drops, renames, or reorders a column) using MissingField.UseNull, name-based (not position-based) references, and defensive typing.
9. Reason about performance: what query folding is, which steps preserve or break it, when to reach for Table.Buffer, and why intermediate queries should load as connection-only.

Prerequisites & connections

Builds on. EX4.01 (Power Query I, Get & Transform), which covered connecting to CSV/Excel/folder sources, the query editor, and the common ribbon transforms: promote headers, change type, filter, split, unpivot, merge, and append. You don't need to have memorised any M from it. You do need to have seen the Applied Steps pane fill up as you clicked, because each of those steps was a line of the code you're about to read and write. You also lean on the Excel Table habit from EX0.01 (Ctrl+T), since every load target and many sources are Excel Tables. And you lean on the type-awareness threaded through EX0.02 and EX1.01. M is strict about the difference between the text "1200.50" and the number 1200.5, and half of error handling is managing that one boundary.

Feeds forward. This is the plumbing beneath everything data-heavy that follows. EX5.01 (Power Pivot & DAX I) consumes the connection-only queries you build here as the tables of a model. A clean M pipeline is the difference between a refreshable model and a manual copy-paste ritual. On the analyst track, the folder-ingestion pattern is how you turn twelve monthly broker statements or four quarterly filings into one analysis-ready table without touching a cell. It's the ingestion spine under M2.05's benchmarking pulls and under any rolling KPI dashboard. It also gives a model its "refresh and it's done" property, which keeps the model a living system instead of a monthly chore. The try … otherwise and quarantine habits you learn here are the same data-integrity reflexes EX2.01 drills at the worksheet level, applied one layer earlier, at ingestion, where catching a bad row is cheapest. When a source system changes a column name six months from now, the robustness patterns you pick up here are what stand between "the refresh threw an error, investigate calmly" and "every downstream number is silently wrong."

Core teaching content

Common mistakes & how experts think differently

1. Forgetting that M is case-sensitive, the signature gotcha of hand-typed M. Table.SelectRows is a function. table.selectrows, TABLE.SELECTROWS, and Table.Selectrows are unknown names, and they error out. Column names in [Brackets] are case-sensitive too, so [revenue] won't find a Revenue column. The GUI never miscapitalises, so this bites the moment you start editing M by hand. An expert has a reflex for it. When a step you've just typed fails with an "unknown identifier" or "column not found" error, suspect capitalisation first, before anything cleverer. M's library uses strict PascalCase.PascalCase, and your typing has to match it.
2. Referencing a renamed step. Rename a step by hand in the Advanced Editor and every later #"Old Name" reference now points at nothing: Expression.Error: The name '#"Old Name"' wasn't recognized. Renaming through the Applied Steps pane avoids this, because Excel rewrites the references for you. An expert renames through the UI. When they do edit code by hand, they rename every occurrence. And they read a "name wasn't recognized" error as "a step reference is stale," instead of suspecting a corrupted file.
3. each-scope confusion, the shadowed _. Inside an each, _ is this row. Nest a second each, say an inner lookup that iterates on its own, and the inner _ shadows the outer one. A bare [Col] inside now refers to the inner context, which isn't the row you meant. You get a silent wrong value or a "column not found." Whenever two contexts are in play, an expert drops the each sugar and writes an explicit (row) => …, with an inner (item) => …. Each parameter then has its own name, and there's no doubt about which [Col] belongs to which.
4. Writing a function that assumes columns a drifting source drops. A per-file transform that hardcodes Table.TransformColumnTypes(…, {{"Discount", type number}}) explodes the month the source stops sending Discount. A beginner writes the transform against this file. An expert writes it against the contract, using MissingField.UseNull, selecting columns by name and checking that the required columns exist. Then one odd file degrades gracefully, and it doesn't detonate the whole refresh. Assume the schema will drift, because over a year it will.
5. Loading intermediate queries instead of keeping them connection-only. Beginners load the file list, the sample file, and three staging queries to their own worksheets. That quadruples the workbook size and the refresh time, and it clutters the file with tables nobody reads. An expert loads only one output table or feeds the model, then marks every staging query Only Create Connection. If a query's job is to feed another query, it should never touch a sheet.
6. Ignoring errors instead of quarantining them. The lazy fix for a dirty column is a blanket try … otherwise 0, and then you move on. That's fine when zero really is the right value. When it isn't, it corrupts your numbers without a sound. A failed price parse that becomes ₹0 understates revenue and leaves no trace. An expert tells "blank means zero" apart from "this shouldn't have failed". The first gets neutralised. The second gets quarantined with Keep Errors and investigated. And the expert monitors the count of error rows as a data-quality metric, instead of burying it as an inconvenience.
7. Trusting the auto-inserted Changed Type step. When you first load data, Power Query helpfully inserts a Changed Type step that hardcodes every current column and its type. Add or remove a source column later and that frozen list is wrong. It's a leading cause of "it broke and I didn't change anything." Experienced hands review that auto step and often delete and rewrite it. That way typing is explicit and covers only the columns that must be typed.
8. Confusing lists and records (braces vs brackets). { } is a positional list, as in L{0}, and [ ] is a named record, as in R[SKU]. Use the wrong one, L[0] or R{SKU}, and it errors immediately. An expert reads the bracket as a type signal. Braces mean "by position," square brackets mean "by name," and a table is a list of records that combines both.

One habit ties all eight together. Treat a query as a program with a contract. Beginners write M that describes the file in front of them. Professionals write M that states what they require, tolerates what they can, and quarantines what they cannot. Then next month's slightly different file either refreshes cleanly or fails loudly and specifically. It never fails in silence and leaves you holding wrong numbers.

Worked examples

Worked example 1: India monthly-statement folder pipeline (India)

The task. A Bengaluru distributor drops one revenue statement into a folder each month, named 2024-MM_Bengaluru.csv. Each file lists a product SKU and its Revenue in ₹. The SKU column arrives with stray spaces at the start or end, and the Revenue column arrives as text. Your job is to build a parameterised per-file cleaning function that trims SKU, types Revenue and stamps a Month from the file name. Then invoke it across the folder, append the results into one table, and report the total revenue and the row count. We'll use three sample files.

Here are the three sample files as they arrive, with the stray spaces in SKU left in so you can see them:

2024-01_Bengaluru.csv        2024-02_Bengaluru.csv        2024-03_Bengaluru.csv
SKU     , Revenue            SKU   , Revenue              SKU  , Revenue
"SKU-01 ", 125000            "SKU-01", 132000             "SKU-01", 141000
"SKU-02" ,  98000            "SKU-02", 105000             "SKU-02", 112000
"SKU-03" , 210000            "SKU-03", 198000             "SKU-03", 225000
                             "SKU-04",  88000

File 1 has 3 rows and file 3 has 3 rows. File 2 has 4 rows, because a new SKU-04 appears. That's mild schema growth, and our name-based pipeline takes it in its stride.

Step 1, the per-file cleaning function. Written once, as a function of one file's binary:

(statement as binary) as table =>
let
    Source   = Csv.Document(statement, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Trimmed  = Table.TransformColumns(Promoted, {{"SKU", Text.Trim, type text}}),
    Typed    = Table.TransformColumnTypes(Trimmed, {{"SKU", type text}, {"Revenue", Int64.Type}})
in
    Typed

Text.Trim strips the stray spaces from every SKU, and Int64.Type turns the text revenues into whole numbers.

Step 2, the driver over the folder. Invoke the function per file, stamp the month from the name, and expand:

let
    Source   = Folder.Files("C:\Statements"),
    KeepCsv  = Table.SelectRows(Source, each Text.EndsWith([Name], ".csv")),
    Invoke   = Table.AddColumn(KeepCsv, "Data", each CleanStatement([Content]), type table),
    AddMonth = Table.AddColumn(Invoke, "Month", each Text.BeforeDelimiter([Name], "_"), type text),
    Expanded = Table.ExpandTableColumn(AddMonth, "Data", {"SKU", "Revenue"}),
    KeepCols = Table.SelectColumns(Expanded, {"Month", "SKU", "Revenue"})
in
    KeepCols

Text.BeforeDelimiter([Name], "_") turns 2024-01_Bengaluru.csv into 2024-01. Because it's added before the expand, every exploded row inherits its file's month.

Step 3, recompute every figure. Work it per file first, then for the append. Every figure here was recomputed and verified in Python.

Total revenue after the pipeline = ₹14,34,000, and total rows = 10. In M these are one line each: List.Sum(KeepCols[Revenue]) = 1434000, and Table.RowCount(KeepCols) = 10.

Step 4, read it. Nothing about "three files" is wired into the logic. Drop 2024-04_Bengaluru.csv into the folder and hit Refresh. The same function cleans April's rows, stamps them with 2024-04 and appends them. The total and row count update themselves. That's the promise of a pipeline. You built a process instead of a spreadsheet. Notice, too, that SKU-04 turning up only in February broke nothing. Every step names its columns and the append unions by name. It's the same schema-drift resilience the mini-project asks you to stress-test.

Worked example 2: US error-row neutralisation and Group By (US)

The task. A US import arrives with an Amount column in dollars that should be numeric. Three of its cells won't parse: N/A, pending, and an em-dash placeholder. Neutralise the error rows with try [Amount] otherwise 0, count how many rows errored, then Group By Category to get clean per-category and total figures. There are ten rows across four regions.

Here's the raw import, with the Amount column as it was received, before any typing:

Category   Amount
East       1200.50
East       3400.00
East       N/A
West       875.25
West       2100.00
West       pending
Central    4500.75
Central    990.00
Central    —
South      1750.00

Step 1, type the column, creating error cells. Table.TransformColumnTypes(Source, {{"Amount", type number}}) succeeds on the seven numeric strings and turns N/A, pending, and , into error values, three of them in all.

Step 2, count the error rows before neutralising, because the count is a data-quality signal you want to keep:

ErrorCount = Table.RowCount(Table.SelectRowsWithErrors(Typed, {"Amount"}))

It keeps only the rows whose Amount errored and counts them, which gives 3. Or right-click the Amount column ▸ Keep Errors and read the row count. You'll get the same answer.

Step 3, neutralise inline, then Group By. Add a clean column that turns errors into 0, then group:

WithClean = Table.AddColumn(Typed, "CleanAmount", each try [Amount] otherwise 0, type number),
Grouped   = Table.Group(WithClean, {"Category"},
              {{"Total", each List.Sum([CleanAmount]), type number}})

Step 4, recompute every figure. Each error cell contributes 0, and every figure was recomputed and verified in Python.

Error rows = 3, clean total = $14,816.50. Now check the grand total longhand. 4,600.50 + 2,975.25 = 7,575.75. Add 5,490.75 and you're at 13,066.50. Add 1,750.00 and you reach $14,816.50. In M, List.Sum(WithClean[CleanAmount]) gives the same 14816.5.

Step 5, read it, and mind the trap. try … otherwise 0 was the right call here for one reason only. A missing amount in these rows contributes nothing to a sum. And we counted and can quarantine the three offenders, so nobody is pretending they never existed. Now suppose Amount had been a price instead of an additive dollar figure. Turning an unparseable price into 0 would understate the model without anyone noticing, and the correct move would be to Keep Errors into a review query and fix the source. The mechanics are identical. The lesson is the judgment call between neutralising and quarantining.

Practice set

Work each problem before you read its solution. Assume Microsoft 365, and assume you can open the Advanced Editor to read or write M. Where a problem asks for a formula, write the M in full.

P1 (guided). Read a let … in. Given the query below, state (a) how many steps it has, (b) which step the query outputs, and (c) what single word you would change to output the unfiltered typed table instead.

let
    Source = Excel.CurrentWorkbook(){[Name="Raw"]}[Content],
    Typed  = Table.TransformColumnTypes(Source, {{"Qty", Int64.Type}}),
    Big    = Table.SelectRows(Typed, each [Qty] > 50)
in
    Big

Solution. (a) Three steps: Source, Typed, Big. (b) The query outputs Big, because the in clause names it. (c) Change the in result from Big to Typed. The in clause alone decides which step's value comes back, so re-pointing it hands you the typed-but-unfiltered table. Steps are named values and in picks the output, which is the let … in model in one problem.

P2 (guided). Desugar an each. Rewrite Table.SelectRows(Source, each [Revenue] > 100000) without the each keyword, and explain in one line what [Revenue] expands to.

Solution. Table.SelectRows(Source, (_) => _[Revenue] > 100000). each is shorthand for a one-argument function (_) => …, and a bare [Revenue] inside it is shorthand for _[Revenue], the value in that column for the current row _. Table.SelectRows applies the function once per row and keeps the rows for which it returns true.

P3 (guided). Write and invoke a custom function. Write a named M function AddCess that takes a number and returns it plus a 4% health-and-education cess, then show the Table.AddColumn line that applies it to an Amount column. Evaluate AddCess(40000).

Solution. AddCess = (amount as number) as number => amount * 1.04. Apply it with Table.AddColumn(Source, "WithCess", each AddCess([Amount]), type number). AddCess(40000) = 40000 × 1.04 = 41,600. The each AddCess([Amount]) is (_) => AddCess(_[Amount]), so the function runs once per row on that row's Amount. The as number annotations are optional, but they make the function reject a bad, non-numeric argument with a clear message.

P4. Total a folder pipeline. A folder holds three daily CSVs cleaned by one function and appended. After cleaning, the Revenue values are as follows. Day1: 4200, 3800. Day2: 5100, 4400, 3900. Day3: 6000, 2500. Write the two M expressions that give the appended row count and total revenue, and evaluate both.

Solution. With the final query named KeepCols, the row count is = Table.RowCount(KeepCols) and the total is = List.Sum(KeepCols[Revenue]). Rows = 2 + 3 + 2 = 7. Total = (4200 + 3800) + (5100 + 4400 + 3900) + (6000 + 2500) = 8,000 + 13,400 + 8,500 = 29,900. The append (Table.Combine, generated by the expand) unions all the files' rows. Both aggregates run over that single combined table, and by this point the file boundaries have vanished.

P5. Neutralise and count errors. A numeric column arrives as 500, err, 750, 1200, bad, 300, and two of those cells won't parse. Write the M to (a) build a clean column that turns errors into 0 and (b) count the error rows. Evaluate the clean total and the error count.

Solution. (a) Table.AddColumn(Typed, "Clean", each try [Amount] otherwise 0, type number), and the two bad cells become 0. Clean total = 500 + 0 + 750 + 1200 + 0 + 300 = 2,750. (b) Table.RowCount(Table.SelectRowsWithErrors(Typed, {"Amount"})) = 2. Count the errors before you neutralise. Once try … otherwise 0 has run there are no error values left to count, because the neutralise step has erased the signal the count needs.

P6. Survive a dropped column. A pipeline does Table.SelectColumns(Source, {"Date","SKU","Discount"}), but one monthly file has no Discount column and the refresh fails. Give the one-argument fix and explain what the Discount column contains for that file afterwards.

Solution. Table.SelectColumns(Source, {"Date","SKU","Discount"}, MissingField.UseNull). With the MissingField.UseNull option, a missing listed column comes back as a column of nulls instead of throwing an error. So the file with no Discount yields a Discount column full of null, and the refresh completes. MissingField.Ignore would omit the column entirely. This is the schema-drift reflex. State what you require, and tell M how to tolerate its absence.

P7. Fix a stale step reference. Someone renamed a step from #"Changed Type" to Typed by hand in the Advanced Editor and now the query errors with the name '#"Changed Type"' wasn't recognized. What went wrong and what is the two-part fix?

Solution. Renaming the step's definition didn't update the later reference to it, so a downstream step still says #"Changed Type", which no longer exists. Fix it one of two ways. Either (1) update every later reference to the new name Typed, or (2) undo and rename through the Applied Steps pane (right-click ▸ Rename), which rewrites all references automatically. A step name is a variable that appears in its definition and in its uses. Hand-edit only one and you leave a dangling reference.

P8. Diagnose a case error. = table.selectrows(Source, each [amount] > 0) fails. Name the two things wrong with it.

Solution. (1) The function name is miscased. M is case-sensitive, so it must be Table.SelectRows, not table.selectrows, and the lowercase name is an unknown identifier. (2) The column reference [amount] is case-sensitive too. If the column is named Amount, it won't be found, and the reference must be [Amount]. Both errors come from the same root. M matches names letter for letter, and the GUI's flawless capitalisation lulls people into sloppiness the moment they type M by hand.

P9. Explain a container access. In the step Excel.CurrentWorkbook(){[Name="Sales"]}[Content], identify each of the three bracket operations and what type it works on.

Solution. Read it left to right. Excel.CurrentWorkbook() returns a table of workbook items. {[Name="Sales"]} is list/row access by a matching record. The {…} selects the row whose Name field equals "Sales", using the record [Name="Sales"] as the match key. Then [Content] is record field access. The selected row is a record, and you read its Content field, which holds the table. So braces { } do positional/row selection, and square brackets [ ] do field access. You've just read both container idioms composed in one expression.

P10. Choose neutralise vs quarantine. For each case, say in one line whether try … otherwise 0 (neutralise) or Keep Errors into a review query (quarantine) is the right call. (a) A LateFee column where a blank legitimately means no fee. (b) A UnitPrice column where three rows failed to parse.

Solution. (a) Neutralise. A missing late fee contributes 0 to any total, so try [LateFee] otherwise 0 is correct and loses nothing. (b) Quarantine. A UnitPrice that failed to parse isn't legitimately zero. Turn it into 0 and you'd understate every line that uses it with no warning, so Keep Errors those three rows into a review query and fix the source. The mechanics are identical. The question is always "is zero the true value, or am I hiding a problem?"

P11 (timed: 6 min). End-to-end mini pipeline. Two files land in a folder. File A's Amount column is 1000, x, 1500. File B's is 2000, 3000, y. The pipeline types Amount to number, neutralises errors with try [Amount] otherwise 0, and appends. Give the appended row count, the clean total, and the error-row count across both files.

Solution. Rows = 3 + 3 = 6. The append keeps every row, and neutralising doesn't drop any. Clean total = (1000 + 0 + 1500) + (2000 + 3000 + 0) = 2,500 + 5,000 = 7,500. Error rows = one per file (x and y) = 2. You can count them with Table.RowCount(Table.SelectRowsWithErrors(Typed, {"Amount"})) before the neutralise step. Watch the row count. It's 6, not 4, because try … otherwise 0 keeps the bad rows with a 0. Remove Errors would have left 4 rows.

P12 (timed: 6 min). Choose the right tool. For each case, name the best M feature or step in one line. (a) Apply the same cleaning to every file in a folder. (b) Stop a dirty numeric cell from erroring a sum. (c) Keep a Discount column reference from breaking when the column is sometimes absent. (d) Make a many-times-referenced table stop re-evaluating. (e) Keep an intermediate query from bloating the workbook.

Solution. (a) A custom function invoked per file via Table.AddColumn(…, each f([Content])), which is the folder-ingestion pattern. (b) try [Amount] otherwise 0, for inline error neutralisation. (c) Table.SelectColumns(…, MissingField.UseNull), which tolerates the missing column. (d) Table.Buffer, which freezes the table in memory, at the cost of stopping folding. (e) Set it to Connection Only (Only Create Connection) and load just the final result, leaving the scaffolding unloaded. Underneath all five, you're matching the symptom to the one feature built for it: repetition, a dirty cell, drift, re-evaluation, bloat.

Applied mini-project

Build a self-refreshing monthly-statements pipeline. Assemble a complete folder-ingestion pipeline that turns a folder of monthly CSV statements into one clean, analysis-ready table that grows by itself when a new month arrives. You shouldn't have to edit a single cell by hand or type in a single month, and no bad row should be left to poison a total.

Set up the source folder.

1. Create a folder and put at least three monthly CSVs in it, named YYYY-MM_<something>.csv, for example 2024-01_Region.csv. Each file needs at least SKU, Revenue, and one deliberately messy column. Give SKU some leading and trailing spaces, and make a few Revenue cells unparseable, such as N/A or a dash. Make the files slightly inconsistent, too. One file should have an extra column the others lack, and one should be missing a column, so the schema drift is real.

Build the pipeline. Read every generated step in the Advanced Editor as you go.

1. Ingest. Go Data ▸ Get Data ▸ From File ▸ From Folder, then filter the file list to .csv with Table.SelectRows(…, each Text.EndsWith([Name], ".csv")).
2. Per-file function. Write a function (statement as binary) as table => … that parses the CSV, promotes headers, trims SKU with Text.TransformColumns(…, {{"SKU", Text.Trim, type text}}), and types the columns. Editing the auto-generated Transform Sample File works too. Reference columns by name, and use MissingField.UseNull so the odd file survives.
3. Invoke & stamp. Add a Data column that invokes the function on [Content]. Then add a Month column with Text.BeforeDelimiter([Name], "_") before you expand, so every row inherits its file's month.
4. Expand & type. Use Table.ExpandTableColumn to unpack into rows, and set the final types explicitly. Don't trust the auto Changed Type.
5. Error routing. Neutralise the additive Revenue errors with try [Revenue] otherwise 0, but only where zero really is correct. Build a parallel Keep Errors query that quarantines the bad rows for review, and report the count of quarantined rows.
6. Robustness & performance pass. Confirm the pipeline still refreshes when you (a) drop a new file into the folder, (b) delete a column from one file, and (c) reorder columns in another. Put folding-friendly steps early. Set every staging query (file list, sample, function) to Connection Only, and load only the final table.

Deliverables: one loaded table (Month, SKU, Revenue, …), a quarantine query with its error-row count, and a one-line total revenue via List.Sum.

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

Pass: ≥ 8/10, with no zero on any criterion. Keep the workbook. Its folder pipeline is the ingestion spine you'll reuse for every recurring multi-file report you ever have to consolidate.

Lab: open the Power Query M pipelines workbook lab in the Practice ▸ Excel tab and pass its checks.

Reading & resources

Function and language references (reach for these first):

• Microsoft Learn, the Power Query M formula language reference and the per-function pages for Table.SelectRows, Table.AddColumn, Folder.Files, Table.ExpandTableColumn, and try/otherwise (search "Power Query M function reference"): authoritative signatures and option records. [Free] [Intermediate]
• Microsoft Learn. "Understanding query evaluation" and "Query folding" articles: the definitive explanation of folding and what breaks it. [Free] [Intermediate]

Deeper practice and pattern libraries:

• Ken Puls & Miguel Escobar, Master Your Data with Power Query in Excel and Power BI, the standard book. Its folder-import and custom-function chapters track what you've learned here closely. [Paid] [Intermediate]
• Gil Raviv, Collect, Combine, and Transform Data Using Power Query (and his blog, DataChant), strong on real-world combine-files and error-handling patterns. [Paid/Free] [Intermediate]
• ExcelIsFun and "Access Analytic" (YouTube), clear walkthroughs of the folder pipeline and the Transform Sample File machinery in motion. [Free] [Beginner]

For the M language specifically:

• The official "Power Query M language specification" (PDF on Microsoft Learn), dry but complete on let … in, each, containers, and functions; skim the sections on expressions and functions. [Free] [Advanced]

Bridge forward: the connection-only tables you build here become the source tables of EX5.01 (Power Pivot & DAX I). Skim its intro once your pipeline refreshes cleanly. A good model starts with the kind of clean, typed, self-refreshing table you've just learned to produce.

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": "M as a real programming language — custom functions, error handling with try/otherwise, and the professional judgment of neutralising an error versus quarantining it for review",
  "use": "The single most consequential decision in this module is not syntax, it is a judgment call M cannot make for you: when an error cell should become a harmless zero and when it should stop the pipeline and demand a human look at it. Use an assistant to draft the mechanical parts — a custom function's signature, the Keep-Errors review query, the row-count assertion after a Group By — and reserve the neutralise-versus-quarantine call for yourself, because it depends on what the number MEANS in the business, which is not visible from the M code alone.",
  "tools": ["Coding assistants — Claude, ChatGPT, or a notebook's inline assistant to draft custom M functions and the equivalent Python try/except", "Chat assistants — for explaining an unfamiliar M error message before you decide how to handle it", "Document/PDF AI — for confirming what a source system's error codes or placeholder text (N/A, pending) actually mean before deciding whether zero is the honest replacement"],
  "prompt": "Here is a column with some values that fail to parse as numbers: <PASTE a few examples>. Write the M (or the pandas equivalent) for two versions: one that neutralises failures to zero inline, and one that quarantines them into a separate review table while still returning the clean rows. Do not decide for me which one is 'right' for this column — that depends on whether zero is a truthful stand-in for the business quantity, which only I can judge.",
  "verify": "Before accepting either version, ask what each of the specific error values (N/A, pending, an em-dash) most likely MEANS in the source system, and confirm that meaning against the source documentation or the person who owns that export — an assistant will readily generate plausible-sounding M for either choice without knowing which one is true.",
  "diy": "The gate is unaided: you write a custom M function with try/otherwise, count error rows with Table.SelectRowsWithErrors before neutralising them, and state in one sentence when neutralising an error to zero is honest and when it silently understates the model — with no tool in the room."
}

Modern Data Analysis

By hand first. You already write a per-file custom function, invoke it across a folder with Table.AddColumn, and neutralise or quarantine error rows with try … otherwise. For this skill the modern layer is the skill itself, because Power Query already is the code-as-recipe tool. So instead of asking how you'd do this in Python, the work here is putting the audit trail the recipe already gives you to real use.

Today's workflow. A refresh re-executes the same function against whatever the source is right now. That makes the error count, Table.RowCount(Table.SelectRowsWithErrors(step, {"Amount"})), a data-quality signal. Don't treat it as a nuisance to neutralise away as fast as possible. If the count rises between refreshes, read that as an alert, before the pipeline launders the errors into zeros without telling you. And keep in mind a limit that comes up again and again. M is the right tool while the transform can be expressed as a folder of files and a chain of table operations. Once the logic needs branching business rules, statistical modelling, or a proper automated test suite reviewed alongside the code, you're better off rebuilding the same pipeline in Python. There, pandas and pytest make this kind of assertion a first-class citizen instead of a one-off M expression.

Tools & sources (IN + US). Use pandas for the equivalent pd.to_numeric(errors=...) neutralise/quarantine contrast and for the row-recovery simulation below. For India, use the same shape of monthly statement folder the first worked example builds. It comes complete with the trimming and typing a Bengaluru distributor's exports actually need. For the US, use a comparable multi-category ledger export, the shape the second worked example's error-row scenario represents.

# recomputed in-session — R3
valid = [1200.50, 3400.00, 875.25, 2100.00, 4500.75, 990.00, 1750.00]
clean_total = sum(valid)
mean_valid = clean_total / len(valid)
plausible_hidden = 3 * mean_valid         # 3 error rows, if typical of the other 7
understated_pct = plausible_hidden / (clean_total + plausible_hidden)
print(round(clean_total, 2), round(understated_pct*100, 2))   # 14816.5, 30.00

Verify. The recomputed clean total is $14,816.50, the same figure the second worked example reached. The 3-error-row count matches the Table.SelectRowsWithErrors result stated there too.

Quantitative lens

Neutralising an error to zero has a computable, plausible exposure, and it is invisible unless you separately track the error count. Start from the second worked example. Its seven valid Amount values average $2,116.64 each. Suppose the three unparseable rows (N/A, pending, an em-dash) had carried amounts typical of the other seven. Nobody can know that, since the values that would tell you are the ones that failed to parse. Their plausible combined contribution would then be $6,349.93. try … otherwise 0 reports the clean total, $14,816.50, as if those three rows contributed nothing. A total that included them at the typical mean would be $21,166.43, so the report carries a potential 30.00% understatement. Nothing flags it. There's no error and no red cell, only a total that's smaller than reality. None of this claims to know what the three rows really were. It measures the size of the risk that otherwise 0 accepts every time you use it on a column where zero isn't a truthful stand-in for "unknown."

MissingField.UseNull is the difference between one bad file costing you nothing and one bad file costing you everything. The flashcards already state the mechanism. Without the option, Table.SelectColumns raises a hard error when a listed column is absent. With it, the missing column simply becomes null. Now apply that to the first worked example's real three-file pipeline: January ₹4,33,000, February ₹5,23,000, March ₹4,78,000, ₹14,34,000 total across 10 rows. Say February's file were missing its Revenue column. It's a real, ordinary kind of schema drift. The default behaviour, with no MissingField.UseNull, errors that file's per-file transform. And because the folder pipeline expands every file's result through the same step, that one error typically fails the whole load. 0 rows, 0% of the true total reaches the sheet. Now put MissingField.UseNull in place. January and March still process correctly. That's ₹9,11,000 of ₹14,34,000, 63.53% of true revenue, and 6 of 10, 60% of rows. February's Revenue comes through as an explicit null. You can see that gap and investigate it. A failure or an understatement you never see is far worse.

Limits: the "typical of the other seven" exposure figure is a plausibility estimate built from the worked example's own valid-row statistics. It makes no claim about the three unparseable rows' true values. Those are unknowable by construction, and that unknowability is the argument for quarantining instead of guessing. The "0% vs 63.53%" contrast assumes that an unhandled per-file hard error aborts the whole load. Power Query documents that as its default failure mode for an unhandled error surfacing through Table.ExpandTableColumn. It doesn't claim that every possible pipeline shape fails the same way.

Where this goes next: galaxy cross-links

• G2 · CS2.03 Databases & SQL for Analysts, the neutralise-vs-quarantine judgment here is the same one a NULL-handling or constraint-violation decision forces in a database pipeline.
• G2 · DA1.03 pandas II: Cleaning, Time Series & the Excel↔Python Bridge, the direct Python-side continuation of the error-handling patterns you've learned here, including pd.to_numeric(errors=...) as the try … otherwise equivalent.
• G2 · QD2.04 Reproducible Research, Deployment & the Quant Stack, the assertion habit this addendum argues for (row counts, expected columns) is the habit that module builds, applied one level earlier, at the import boundary.

Teach it back & journal

Feynman prompt. Explain to a colleague who only knows the Power Query ribbon what the let … in code behind their query really is, using the metaphor of a numbered recipe. Each numbered line is a bowl with a name, like Source, Typed or Filtered. Each bowl is made by taking the previous bowl and doing one thing to it, and the last line of the recipe says which bowl to serve. Then explain the one twist that trips people up. What happens when you rename a bowl at the top of the recipe but a later line still asks for the old name? And why does the "Applied Steps" pane rename safely when hand-editing the code doesn't? You may use the words step-is-a-variable, each, and quarantine only in your last two sentences, and you should attach each one to the moment in the recipe story it describes.

Journal prompt. Find one recurring data chore that you or a colleague do by hand (consolidating monthly exports, cleaning a downloaded report, stacking regional files). Dissect it for two things: (1) the repetition that a per-file function plus a folder pipeline would erase, and (2) the dirty rows you currently fix by hand or, worse, ignore. Write down what the pipeline's steps would be. For each messy column, decide whether the right response is to neutralise it, because zero really is correct, or to quarantine it, because a bad value is hiding a problem. Note what would have to change in your source for the pipeline to break. Then write the one sentence you want to remember about why building a process that refreshes beats maintaining a spreadsheet you redo by hand. Close with the one habit you'll keep for good. An error row is a question about your source, never a number to bury.

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.