Custom function in Power Query

A function in M is just a value, the same as a number or a table. So you define one the way you define any step, by binding a name to it in the let. The body after => is the function, and the part before it is the parameter list.

let
    AddGST = (amount as number) as number => amount * 1.18,
    Example = AddGST(1000)          // evaluates to 1180
in
    Example

(amount as number) as number => amount * 1.18 reads: take one argument, amount, which must be a number. Return a number. The result is amount * 1.18. The as number type annotations are optional, but professionals use them. They document intent, and they make M reject a bad call, such as text passed where a number is required, with a clear message instead of a downstream mystery. A quick throwaway function can drop them, and (x) => x * 1.18 is legal too. You might be tempted to test it live with Home ▸ Enter Data. Don't. The clean way to test a function is to type an invocation as the in result, or to use Add Column ▸ Invoke Custom Function.

The point of a function is reuse. To apply AddGST to every row of an Amount column, add a column whose formula invokes it and passes in the current row's field:

Table.AddColumn(Source, "WithGST", each AddGST([Amount]), type number)

Here each AddGST([Amount]) is (_) => AddGST(_[Amount]). For each row, it feeds that row's Amount into AddGST. Run a few values through it. Amount values of 1000, 2500, and 990 give a new column holding 1180.00, 2950.00, and 1168.20. The optional trailing type number declares the new column's type, so you don't need a separate Change Type step.

How do parameters turn a function into a template?

Parameters are what make a function a template. A function can take more than a single value. It can take a whole file. The most important function you'll write here has the signature (statement as binary) as table => …. It accepts one file's raw bytes and returns one clean table, and it's the seed of the folder pipeline covered in combining files from a folder. The mental leap is that "clean one file" and "clean a hundred files" are the same code. You write the cleaning once, as a function of one file, then invoke it once per file. Parameterisation is what turns a one-off transform into a pipeline.

You'll rarely write the file-cleaning function from scratch. When you use Combine Files on a folder, Power Query generates it for you as a helper called Transform Sample File. That's a function, (Parameter1 as binary) as table => …, built from the transforms you demonstrated on one sample, and you edit it like any other query. So the skill is less "write M functions from a blank page" and more "read, trust, and extend the function the GUI wrote."