Parameters, Custom Functions and Reusable Pipelines

Introduction and Context

Power Query is most valuable when it is treated not merely as a tool for cleaning one table, but as a transformation engine for repeatable data-integration processes. In a small demonstration, a user may filter rows, split columns and change data types manually. In a production environment, however, the same transformation may need to be applied to hundreds of files, multiple business units or changing reporting periods.

Parameters, custom columns, custom functions and reusable pipelines address this scalability problem. A parameter separates configuration from transformation logic. A custom column adds derived business logic to a table. A custom function packages a sequence of steps so that it can be called repeatedly. A reusable pipeline establishes a consistent architecture for ingesting, validating, transforming and delivering data.

These capabilities are implemented through Power Query M, a functional language designed for data transformation. Although many tasks can be completed through the graphical interface, professional users must understand the underlying logic, especially when solutions are intended for scheduled refresh, governance, audit and handover to other developers.

Learning Objectives

By the end of this chapter, the learner should be able to:

1. Explain the purpose and scope of Power Query parameters.
2. Create parameters for configurable file paths, dates, thresholds and environments.
3. Design reliable custom columns using M expressions.
4. Explain the structure and evaluation of custom functions.
5. Build a function for transforming a standard file template.
6. Apply a function to a folder of files and combine the results.
7. Design reusable and maintainable integration pipelines.
8. Identify common errors, governance risks and performance limitations.

1. Parameters: Separating Configuration from Logic

A parameter is a named value that can be changed without rewriting the transformation steps that use it. Typical parameters include:

• a folder path;
• a reporting start or end date;
• a minimum sales threshold;
• a business-unit code;
• a file name pattern;
• a development, test or production environment identifier.

For example, a query may contain the expression:

Source = Folder.Files("C:\\Reports\\2025")

This expression works only when the folder exists at that exact location. A more maintainable design defines a parameter named pFolderPath and uses:

Source = Folder.Files(pFolderPath)

The transformation logic remains unchanged when the data location changes.

1.1 Parameter properties

A Power Query parameter generally has a name, data type, current value and, optionally, a list of permitted values or a suggested value. Common types include Text, Number, Date, Date/Time and Logical.

A parameter should represent configuration, not a value that changes for every row. A row-level calculation belongs in a custom column or function.

1.2 Parameter design principles

A good parameter has a clear name, appropriate data type, documented purpose and a safe default value. Names such as pStartDate and pFolderPath are preferable to ambiguous names such as Value1. Parameters should also be validated. If a parameter identifies a folder, the query should fail with an understandable message when the folder is unavailable rather than silently returning an empty result.

A parameter can be used in a filter:

FilteredRows = Table.SelectRows(
    Source,
    each [OrderDate] >= pStartDate
)

The expression assumes that OrderDate is already typed as Date or Date/Time and that pStartDate is compatible. Explicit data typing is therefore essential.

Parameters are not automatically a security mechanism. A parameter containing a database password should not be used as a substitute for a governed credential store. Authentication should be managed through Power BI or the relevant data platform.

2. Custom Columns and Row-Level Business Logic

A custom column computes a value for each row. In the Power Query interface, the user can select Add Column > Custom Column and enter an M expression. The row context is represented by an underscore record, commonly written as each.

For example:

= Table.AddColumn(
    PreviousStep,
    "NetAmount",
    each [Quantity] * [UnitPrice] - [Discount],
    type number
)

The expression calculates a net amount for every row. It assumes that Quantity, UnitPrice and Discount are numeric and non-null, or that null handling has been designed.

2.1 Conditional logic

M supports conditional expressions using if ... then ... else:

= Table.AddColumn(
    PreviousStep,
    "OrderBand",
    each if [NetAmount] >= pMinimumAmount
         then "High Value"
         else "Standard",
    type text
)

Multiple conditions can be nested, although excessive nesting reduces readability. A clearer approach may be to create a classification function or use a lookup table maintained by the business.

2.2 Nulls and errors

Null is not the same as zero, an empty string or an error. A robust custom column explicitly considers null values:

= Table.AddColumn(
    PreviousStep,
    "SafeAmount",
    each if [Amount] = null then 0 else [Amount],
    type number
)

For potentially invalid operations, try ... otherwise can prevent one malformed value from stopping the entire query:

= Table.AddColumn(
    PreviousStep,
    "ParsedDate",
    each try Date.From([DateText]) otherwise null,
    type date
)

Error suppression should not become error concealment. A production pipeline should retain an error indicator or exception table so that invalid records can be investigated.

2.3 Text and date transformations

Useful expressions include:

Text.Upper([CustomerName])
Text.Trim([ProductCode])
Date.Year([OrderDate])
Date.Month([OrderDate])
Duration.Days([EndDate] - [StartDate])

The last expression returns the number of days in a duration. It assumes that both values are compatible dates or date/time values. Culture must also be considered when converting text to dates or numbers. For example, 01/02/2025 may represent 1 February or 2 January depending on regional conventions. Explicit culture arguments, such as Date.FromText(TextValue, "en-GB"), can reduce ambiguity.

3. Custom Functions in M

A custom function is a reusable block of logic that accepts inputs and returns an output. Its general form is:

(parameter1 as type, parameter2 as type) as returnType =>
    expression

For example:

(amount as nullable number, rate as number) as nullable number =>
    if amount = null then null else amount * (1 - rate)

This function accepts an amount and a discount rate and returns the discounted amount. The type declarations communicate intended usage and can help reveal incompatible inputs.

A function can be stored as a separate blank query. Suppose a query is named fnCleanSales. It can be invoked as follows:

CleanedTable = fnCleanSales(SourceTable)

The function should normally perform one coherent responsibility. A function named fnCleanSales might standardize column names, trim text, set data types and calculate a net amount. It should not also connect to unrelated systems, create reports and apply arbitrary user-interface formatting.

3.1 Function scope and let expressions

Most practical functions use a let ... in expression:

(tbl as table) as table =>
let
    Trimmed = Table.TransformColumns(
        tbl,
        {{"Customer", Text.Trim, type text}}
    ),
    Typed = Table.TransformColumnTypes(
        Trimmed,
        {{"Quantity", Int64.Type}, {"UnitPrice", type number}}
    ),
    Added = Table.AddColumn(
        Typed,
        "Amount",
        each [Quantity] * [UnitPrice],
        type number
    )
in
    Added

The identifiers in the let block are evaluated as a sequence of named expressions. The expression after in is the value returned by the function. The input parameter tbl is expected to be a table containing the referenced columns.

3.2 Function contracts

A professional function should have a clear contract:

• Inputs: names, types and required columns;
• Processing: transformations and assumptions;
• Output: type and structure of the result;
• Exceptions: treatment of missing columns, invalid values and empty input;
• Dependencies: parameters, lookup tables or external sources.

For example, a file transformation function may require the columns Date, Product, Quantity and UnitPrice. If a supplier changes UnitPrice to Price, the function may fail. A robust design either validates the schema and returns a meaningful error or uses a documented mapping process.

4. Dynamic File Ingestion

Dynamic file ingestion applies the same transformation to files that share a structure. This is commonly used for monthly sales workbooks, daily CSV exports and branch-level files.

The general pattern is:

1. Connect to a folder.
2. Retrieve file metadata and binary content.
3. Filter out temporary or irrelevant files.
4. Invoke a transformation function for each file.
5. Expand the returned tables.
6. Add source-file metadata.
7. Validate the combined result.

A simplified folder query is:

let
    Source = Folder.Files(pFolderPath),
    VisibleFiles = Table.SelectRows(
        Source,
        each [Attributes]?[Hidden]? <> true
            and Text.EndsWith([Extension], ".csv")
    ),
    WithData = Table.AddColumn(
        VisibleFiles,
        "Data",
        each fnTransformFile([Content]),
        type table
    ),
    Expanded = Table.ExpandTableColumn(
        WithData,
        "Data",
        {"OrderDate", "Product", "Quantity", "Amount"},
        {"OrderDate", "Product", "Quantity", "Amount"}
    )
in
    Expanded

The binary [Content] column contains each file. The function must convert that binary content into a table. For CSV files, a function may begin with:

Source = Csv.Document(
    FileBinary,
    [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
)

It can then promote headers, select columns and apply types. Encoding and delimiter settings must match the supplier’s file specification.

4.1 Template-based transformations

A template transformation is a standard sequence applied to every input file. For example:

(FileBinary as binary) as table =>
let
    Raw = Csv.Document(
        FileBinary,
        [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
    ),
    Headers = Table.PromoteHeaders(Raw, [PromoteAllScalars = true]),
    Selected = Table.SelectColumns(
        Headers,
        {"OrderDate", "Product", "Quantity", "UnitPrice"}
    ),
    Typed = Table.TransformColumnTypes(
        Selected,
        {
            {"OrderDate", type date},
            {"Product", type text},
            {"Quantity", Int64.Type},
            {"UnitPrice", type number}
        }
    ),
    Added = Table.AddColumn(
        Typed,
        "Amount",
        each [Quantity] * [UnitPrice],
        type number
    )
in
    Added

This function assumes every file has the same headers and compatible content. A template is not a guarantee of quality; it is a documented contract that must be monitored.

4.2 Preserving lineage

When files are combined, the source file should usually be retained. The [Name], [Folder Path] and [Date modified] fields can support audit and troubleshooting. A source column allows an analyst to identify which file produced a suspicious record.

5. Reusable Pipeline Architecture

A reusable pipeline separates concerns. A practical architecture contains the following layers:

This separation improves maintainability because a change in the source location does not require rewriting business calculations. It also makes testing easier: each layer can be inspected independently.

A query should avoid unnecessarily repeating expensive operations. If the same source is referenced by many downstream queries, designers should understand whether Power Query evaluates the source repeatedly and whether the architecture is appropriate for the data volume. Staging queries, referenced queries and carefully designed source systems can reduce duplicated work, but Table.Buffer should not be used automatically. Buffering can increase memory use and may prevent query folding.

5.1 Query folding

Query folding occurs when Power Query translates transformations into operations performed by the source system, such as a SQL database. Filtering early and selecting only required columns can improve performance when folding is available. Custom M logic, certain joins and non-foldable transformations may cause folding to stop.

Folder and file transformations generally execute in Power Query rather than in the file system as a database would. Consequently, reducing the number and size of files, filtering metadata before opening binaries and archiving obsolete files are important performance practices.

6. Worked Application: Monthly Branch Sales

An organisation receives one CSV file per branch each month. Every file should contain OrderDate, Product, Quantity and UnitPrice. The business wants a consolidated table containing sales amount and the branch inferred from the file name.

Background

The existing process requires an analyst to open each file, copy its contents into a master workbook and manually calculate sales. It is slow and vulnerable to inconsistent formulas.

Problem

Files occasionally contain temporary files, inconsistent whitespace and invalid dates. The process must be configurable for different reporting folders and must preserve file lineage.

Approach

The analyst creates:

1. pFolderPath, a text parameter;
2. fnTransformFile, a function accepting binary file content;
3. a folder query that filters CSV files and invokes the function;
4. a validation step for invalid dates and missing values;
5. an output table containing source file name and branch.

A branch extraction expression might be:

Branch = Text.BeforeDelimiter([Name], "_")

This assumes that a file named North_2025-02.csv follows the agreed naming convention. If names do not follow the convention, the expression should return an error flag rather than silently assign the wrong branch.

The file function performs header promotion, column selection, trimming, type conversion and calculation of Amount. The folder query filters out hidden files and applies the function to [Content]. After expansion, a validation column identifies rows where OrderDate or Quantity is null.

Outcome

The monthly process becomes a refresh operation rather than a copy-and-paste exercise. A new branch file is included automatically when placed in the configured folder. The source name allows reconciliation, and the exception records provide an operational queue for correction.

Learning points

The main benefit is not simply automation. It is the separation of configuration, ingestion, transformation and validation. The organisation can change the folder location or reporting period without rewriting the core transformation. However, automation remains dependent on the file contract; changes to headers or delimiters must be governed and tested.

7. Testing, Governance and Professional Practice

Reusable pipelines require more than technically correct M code. They require controls.

Schema validation

Check that required columns exist before transformation. A simple approach is to compare the list of required names with Table.ColumnNames. Missing columns should generate a descriptive error or an exception result.

Data-quality checks

Useful checks include:

• row counts before and after filtering;
• duplicate business keys;
• null values in required fields;
• negative or impossible quantities;
• dates outside the reporting period;
• unexpected file extensions;
• reconciliation of totals with source-system totals.

Security and privacy

Folder permissions, database credentials and gateway settings must follow organisational access controls. Sensitive fields should not be copied into unnecessary outputs. Parameters must not expose credentials in query text, reports or source-control repositories.

Documentation and version control

Document parameter meanings, source contracts, expected columns, refresh frequency and known exceptions. Store M scripts and deployment metadata under an appropriate change-management process where possible. A function should be readable by another analyst; meaningful names and comments are more valuable than clever but opaque expressions.

Error handling

A pipeline should distinguish between a missing file, an invalid row and a transformation defect. try ... otherwise is useful for capturing row-level errors, but broad error suppression may produce incomplete reports. A better pattern is to produce an error value, retain the source identifier and route the record to an exception table.

8. Limitations and Contemporary Practice

Parameters do not remove the need for environment management. A local path may work in Desktop but fail after publication because the service cannot access the local folder. Gateway configuration or a cloud-accessible source may be required.

Custom functions can increase reuse but also increase complexity. If a function has too many optional arguments or hidden dependencies, it becomes difficult to test. File-combination solutions can also become slow when thousands of large files are opened during every refresh.

Modern data platforms increasingly use dataflows, lakehouses, semantic models and deployment pipelines. Power Query remains relevant because the same transformation concepts apply across these environments. AI-assisted development may suggest M expressions, but generated code must be tested for type correctness, privacy implications, query-folding effects and business-rule accuracy. Human review remains necessary, particularly where transformations affect regulated reporting or financial decisions.

Best-Practice Checklist

Before publishing a parameterised or reusable pipeline, confirm that:

• configuration values are parameters rather than hard-coded constants;
• parameters have clear names, types and documented defaults;
• custom functions have explicit inputs and outputs;
• source schemas and file contracts are documented;
• file metadata and lineage are retained where appropriate;
• data types are applied explicitly;
• nulls and conversion errors are handled deliberately;
• required columns and row counts are validated;
• credentials are managed outside transformation logic;
• performance and query folding have been considered;
• test files include normal, empty and malformed cases;
• exceptions are visible rather than silently discarded.

Chapter Summary

Parameters make Power Query solutions configurable by separating values such as paths, dates and thresholds from transformation logic. Custom columns express row-level calculations and conditions, while custom functions package repeatable logic with defined inputs and outputs. These functions are especially valuable for dynamic file ingestion, where a folder query can apply one template transformation to many files.

A professional reusable pipeline separates configuration, ingestion, standardisation, transformation, validation and output. Reliable solutions preserve lineage, apply explicit data types, handle nulls and errors deliberately, and validate source schemas. Performance depends on filtering early, reducing unnecessary data movement, understanding query folding and avoiding uncritical use of buffering. Governance requires controlled credentials, documentation, testing, privacy protection and transparent exception handling. The central design principle is that automation should improve consistency without hiding data-quality problems.

Key Terms

Parameter: A named configurable value used by a query or function.

Custom column: A column calculated from an M expression for each row.

Custom function: Reusable M logic that accepts inputs and returns an output.

M: The functional language used by Power Query.

Let expression: An M structure that defines named intermediate expressions and returns a final value.

Function contract: The documented inputs, outputs, assumptions and exception behaviour of a function.

Folder connector: A connector that retrieves files and metadata from a folder.

Binary: The data type representing file content before it is parsed.

Template transformation: A standard transformation sequence applied to similarly structured inputs.

Query folding: Translation of Power Query operations into operations performed by the source system.

Data lineage: Information showing the origin and movement of data.

Schema: The expected structure, names and types of a dataset.

Exception table: A table containing records or files that failed validation or transformation.

Null: A missing or undefined value, distinct from zero, empty text and an error.

Type conversion: Changing a value from one data type to another.

Staging query: An intermediate query used to prepare or expose source data for downstream queries.

Refresh: Re-execution of a query to obtain current data.

Parameterisation: The practice of replacing hard-coded configuration with parameters.

Reusable pipeline: A structured transformation process designed for repeated execution and maintenance.

Review Questions

Short Answer Questions

1. What is the main purpose of a Power Query parameter?
2. What does the each keyword commonly represent in a custom column expression?
3. Why should a file-combination query retain the source file name?
4. What is meant by a function contract?
5. Distinguish a null value from a transformation error.

Long Answer Questions

1. Explain how parameters improve the maintainability of a Power Query solution. Include at least three practical examples.
2. Describe the structure of a custom function in M and explain the purpose of its inputs, let block and returned expression.
3. Explain the stages of a dynamic folder-ingestion pipeline.
4. Discuss the role of query folding and identify situations in which it may not apply.
5. Evaluate the governance controls required for a reusable Power Query pipeline.

Analytical / Application Questions

1. A company receives monthly CSV files whose headers occasionally change. Design a parameterised pipeline that detects missing columns, preserves file lineage and produces an exception table.
2. A custom function works for individual files but fails when applied to a folder containing one empty file and one file with an invalid date. Explain how you would redesign the function and validation process.
3. A report works in Power BI Desktop using a local folder path but fails after publication. Analyse the likely causes and recommend an appropriate deployment design.

Multiple Choice Questions with Answers

Question 1

What is the primary purpose of a Power Query parameter?

A. To store a value that can be changed without rewriting transformation logic

B. To replace all data types with text

C. To create a visual in a Power BI report

D. To encrypt source files

Correct Answer: A. To store a value that can be changed without rewriting transformation logic

Question 2

Which M expression correctly adds an Amount column calculated as Quantity multiplied by UnitPrice?

A. Table.AddColumn(T, "Amount", each [Quantity] * [UnitPrice], type number)

B. Table.AddColumn(T, "Amount", [Quantity] * [UnitPrice], type text)

C. Table.AddColumn(T, each "Amount", Quantity + UnitPrice)

D. Table.AddColumn(T, "Amount", Amount = Quantity * UnitPrice)

Correct Answer: A. Table.AddColumn(T, "Amount", each [Quantity] * [UnitPrice], type number)

Question 3

What does the expression try Date.From([DateText]) otherwise null do?

A. Converts every value to text

B. Returns null when the date conversion fails

C. Stops the query whenever one value is invalid

D. Deletes rows containing dates

Correct Answer: B. Returns null when the date conversion fails

Question 4

Which input type is normally used for the content of a file in a folder query?

A. record

B. logical

C. binary

D. duration

Correct Answer: C. binary

Question 5

What is the correct general structure of a one-parameter M function?

A. parameter => expression

B. parameter = expression => table

C. function(parameter) := expression

D. return parameter from expression

Correct Answer: A. parameter => expression

Question 6

Why is explicit data typing important in a custom column calculation?

A. It guarantees that all values are positive

B. It helps ensure operations use compatible value types

C. It removes the need for validation

D. It prevents parameters from being used

Correct Answer: B. It helps ensure operations use compatible value types

Question 7

Which sequence best represents dynamic file ingestion?

A. Create visuals, export images, delete metadata, refresh

B. Retrieve files, filter them, invoke a transformation function, expand results, validate

C. Rename files, compress them, create a dashboard, change credentials

D. Apply formatting, publish immediately, then inspect the source

Correct Answer: B. Retrieve files, filter them, invoke a transformation function, expand results, validate

Question 8

What is query folding?

A. Compressing a Power BI file into a smaller archive

B. Translating Power Query transformations into operations performed by the source system

C. Combining two unrelated reports

D. Converting a table into a chart

Correct Answer: B. Translating Power Query transformations into operations performed by the source system

Question 9

Which practice best supports data lineage in a folder-combination solution?

A. Removing all file metadata after expansion

B. Retaining the source file name or path with the transformed rows

C. Using anonymous credentials

D. Replacing source names with random numbers

Correct Answer: B. Retaining the source file name or path with the transformed rows

Question 10

Why can broad use of try ... otherwise be dangerous?

A. It always causes query folding

B. It may hide data-quality problems by suppressing errors

C. It changes every number to a date

D. It prevents custom functions from returning tables

Correct Answer: B. It may hide data-quality problems by suppressing errors