In Power Query, how do you turn a parameterized query into a reusable M function?

## A function is a query with parameters Everything in M is an expression, and a function is an expression too — a lambda written with `=>`: ``` (fileName as text) as table => let Source = Csv.Document(File.Contents("C:\drop\" & fileName)), Promoted = Table.PromoteHeaders(Source) in Promoted ``` A query whose whole expression is that lambda appears in the Queries pane as a function rather than a table, and it can be called from any other query. The type annotations (`as text`, `as table`) are optional but worth writing: they document intent and produce a clearer error when someone passes the wrong thing. ## The practical route: build one, then generalise Hand-writing M is not the usual path. The idiomatic sequence is: 1. Build a query that works for exactly one input — one file, one API page, one date — with the input hardcoded. 2. Create a Power Query **parameter** (a small query holding a single value with a declared type) and replace the hardcoded literal with it. 3. Turn that query into a function. Power Query rewrites the query as a lambda whose argument is the parameter, leaving the body of your steps intact. The payoff is that all your Applied Steps — the ones you debugged visually against a real sample — become the body of the function. Fixing the transformation later means editing one query, not twelve. ## Invoking it The common invocation is per-row over a driver table: a folder listing, a list of dates, a list of account IDs. ``` Invoked = Table.AddColumn(Files, "Data", each fnLoadFile([Name]), type table), Expanded = Table.ExpandTableColumn(Invoked, "Data", {"OrderID","Amount"}) ``` `Table.AddColumn` produces a column where every cell is a table; `Table.ExpandTableColumn` unpacks the chosen columns and multiplies rows accordingly. You can also invoke a function once for a single value, which is how parameterised date ranges or environment switches are usually wired. ## What Power Query generates for you Combining files from a folder produces this pattern automatically: a sample-file query pointing at the first file, a **Transform Sample File** query holding the steps you edit, a small function that applies those steps to any file's binary, and the main query that invokes the function over the folder listing and expands. Knowing this is the same custom-function pattern — and that you edit the *sample* query, not the output — is what separates someone who uses the wizard from someone who can repair it when a file breaks. ## The costs an interviewer wants named **Per-row invocation.** The function runs once per row of the driver table. A hundred files means a hundred file reads; a thousand account IDs against a web API means a thousand HTTP calls, subject to the API's rate limits and each with its own latency. This is fine for tens of files, painful for thousands of API calls, and it is the reason a "loop over IDs" pattern that works in development times out in the Service. **Folding.** A custom M function generally cannot be translated into the source's native query, so invoking it against a relational source ends query folding for that query — the source stops filtering and the mashup engine does the rest. If the function's body is doing something a database could do, the transformation belongs upstream in a view instead. **Evaluation is not a loop you control.** M is a functional, lazily evaluated language: you cannot rely on invocation order, and referenced queries may be evaluated more than once during a single refresh. Do not build a function whose correctness depends on running in sequence, accumulating state, or being called exactly once. **Errors are per invocation.** If one file in a folder is malformed, its invocation errors while others succeed. Wrapping the body's risky part in `try … otherwise` — or wrapping the invocation itself — lets one bad file become a marked row rather than a failed refresh. ## When a function is the wrong tool If the same function is needed by several reports, copying its M into each `.pbix` recreates the maintenance problem it was meant to solve — the code lives inside binary report files with no review and no version history. The shared-logic answer is to move it up: a dataflow that other reports consume, or a view or transformation in the source system. Reserve custom functions for shaping that is genuinely local to one model, or for connector-only sources — folders of files, paginated web APIs — that nothing upstream can reach.