Reusable Queries, Parameters, and Custom Functions

Introduction

Power Query is most valuable when transformations are designed as reusable data-processing components rather than as isolated steps created for one report. In a small workbook, duplicating a query may appear convenient. In a growing Power BI solution, however, duplication produces inconsistent rules, difficult maintenance, slower development, and increased risk of error. Reusable queries, parameters, templates, and custom functions provide a systematic way to build transformation processes that can be applied to multiple files, tables, periods, or business units.

This chapter examines how to design such processes using Power Query and the M language. The focus is practical: creating staging and referenced queries, using parameters to control behaviour, developing custom functions, and applying these techniques to scalable data-import patterns. The central design principle is separation of concerns: data acquisition, transformation, validation, and presentation should be understandable and independently maintainable.

Learning Objectives

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

1. Distinguish source, staging, referenced, and final queries.
2. Explain how query references support maintainability and reduce duplication.
3. Create and use parameters for folders, dates, environments, and business rules.
4. Construct custom functions in the M language.
5. Apply a function to a folder of files or a table of inputs.
6. Create reusable query templates and document their assumptions.
7. Evaluate performance, governance, security, and reliability issues in reusable Power Query solutions.

1. The Architecture of Reusable Queries

A query is a named sequence of M expressions that retrieves, shapes, and returns data. Power Query records each transformation as an applied step, but the underlying M expression forms a functional program. This makes it possible to treat queries as reusable building blocks.

A maintainable solution commonly contains four layers:

The layers are logical rather than mandatory physical objects. A small query may combine them, while an enterprise solution may separate them into many queries. The objective is clarity and controlled reuse.

1.1 Duplicate versus Reference

Power Query provides two important ways to create another query from an existing query:

• Duplicate creates a separate copy of the query's M code. Subsequent changes to the original do not automatically flow into the duplicate.
• Reference creates a new query whose starting point is the result of another query. Changes to the referenced query normally flow into the dependent query.

For example, suppose stgSales contains standardised sales records. Two outputs may reference it:

• fctSales, containing transaction-level records;
• SalesExceptions, containing rows with missing product codes or invalid quantities.

This is preferable to independently importing and cleaning the same source twice. The common preparation logic exists once, while downstream queries express different purposes.

A reference establishes a dependency. If Query B references Query A, then B cannot correctly evaluate without A. Dependencies can be inspected through the query-dependency view. A well-designed dependency graph generally flows from sources toward outputs and avoids circular references.

1.2 Staging Queries

A staging query is an intermediate query designed to make incoming data consistent and trustworthy. Typical staging operations include:

• removing report titles, notes, and blank rows;
• promoting the correct header row;
• trimming and cleaning text;
• applying explicit data types;
• standardising column names;
• replacing known source-specific values;
• retaining source identifiers and audit fields.

A staging query should normally avoid report-specific calculations. For example, a query that standardises dates and quantities is reusable; a query that calculates “Top 10 products for the current dashboard” is better placed downstream.

Naming conventions improve navigation. A practical convention is src_ for sources, stg_ for staging, ref_ for reusable references, fn_ for functions, and out_ for final outputs. Organisations may choose different conventions, but consistency is more important than the exact prefixes.

2. Parameters as Controlled Inputs

A parameter is a named value that can be changed without editing the transformation logic. Parameters are useful when the same query must operate in different environments, time periods, folders, or operating modes.

Common parameters include:

A parameter should have a clear name, data type, permitted values where appropriate, and a documented default. Parameters improve portability, but they do not automatically make a solution secure. A folder path or server name may change, while credentials remain controlled by Power BI's data-source settings and organisational policies.

2.1 Parameterising a Folder Source

The following M expression uses a text parameter to obtain files from a folder:

let
    Files = Folder.Files(pFolderPath),
    VisibleFiles = Table.SelectRows(
        Files,
        each [Attributes]?[Hidden]? <> true
            and Text.EndsWith([Extension], ".csv")
    )
in
    VisibleFiles

The expression filters hidden files and restricts the result to CSV files. The optional-access syntax ?[Hidden]? helps prevent an error if an attribute is absent. The parameter can be changed when the report is moved from development to production.

2.2 Date Parameters

Dates should be created as date values rather than text whenever possible. For example, a date filter may be expressed as:

Table.SelectRows(
    Sales,
    each [OrderDate] >= pStartDate and [OrderDate] <= pEndDate
)

This assumes that OrderDate, pStartDate, and pEndDate are all compatible date or datetime values. If a datetime column is compared with a date parameter, conversion may be needed, such as Date.From([OrderDate]). Explicit typing avoids silent or culture-dependent comparisons.

3. Custom Functions in M

A custom function is an expression that accepts input values and returns an output. Functions are particularly useful when the same transformation must be applied repeatedly to different files, tables, or parameter values.

The general conceptual form is:

(input as type) as return_type =>
    expression

For example:

(fileContent as binary, fileName as text) as table =>
let
    Raw = Csv.Document(
        fileContent,
        [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
    ),
    Promoted = Table.PromoteHeaders(Raw, [PromoteAllScalars = true]),
    Typed = Table.TransformColumnTypes(
        Promoted,
        {
            {"OrderDate", type date},
            {"Quantity", Int64.Type},
            {"SalesAmount", Currency.Type}
        }
    ),
    AddedFileName = Table.AddColumn(
        Typed,
        "SourceFile",
        each fileName,
        type text
    )
in
    AddedFileName

The function accepts file contents as binary data and a file name as text, then returns a table. The type annotations communicate intended inputs and outputs. They also make errors easier to diagnose.

3.1 Function Invocation

If the function is named fnTransformSalesFile, it can be invoked as follows:

fnTransformSalesFile([Content], [Name])

When used in a file-combination query, this expression is often added as a custom column to the table returned by Folder.Files. The resulting tables can then be expanded.

3.2 Error Handling in Functions

A function should define how it behaves when an input is missing or malformed. The try ... otherwise construct can return a controlled alternative:

(value as nullable number) as nullable number =>
    try Number.From(value) otherwise null

This function converts a value to a number and returns null if conversion fails. Although this prevents refresh failure, indiscriminately replacing errors with null can conceal data-quality problems. A stronger design records an error flag or maintains an exceptions query.

A diagnostic function might return a record rather than only a cleaned value:

(value as any) as record =>
let
    Attempt = try Number.From(value),
    Result = [
        Value = if Attempt[HasError] then null else Attempt[Value],
        HasError = Attempt[HasError],
        ErrorMessage = if Attempt[HasError] then Attempt[Error][Message] else null
    ]
in
    Result

This approach preserves information for quality monitoring, although it creates additional columns and processing steps.

4. The Folder-Combine Pattern

A common business requirement is to combine monthly files that have a consistent structure. The reusable pattern is:

1. Obtain the folder path from a parameter.
2. List files using Folder.Files.
3. Filter hidden, temporary, or irrelevant files.
4. Invoke a custom function for each retained file.
5. Expand the returned tables.
6. Add source-file metadata.
7. Apply final validation and typing.

Conceptually, the process is:

Folder → File inventory → Filter → Function per file → Expand → Validate → Output

The function should contain file-level logic, such as opening the CSV, promoting headers, and standardising columns. The outer query should contain inventory and orchestration logic. This separation allows the function to be tested on one file and reused across the folder.

A simplified orchestration query is:

let
    Files = Folder.Files(pFolderPath),
    Selected = Table.SelectRows(
        Files,
        each [Attributes]?[Hidden]? <> true
            and [Extension] = ".csv"
    ),
    WithData = Table.AddColumn(
        Selected,
        "Data",
        each fnTransformSalesFile([Content], [Name]),
        type table
    ),
    Combined = Table.Combine(WithData[Data])
in
    Combined

This example assumes that every selected file is compatible with the function's expected schema. In practice, a validation step should identify files with missing columns or unexpected formats.

5. Query Templates and Standardisation

A query template is a reusable pattern that defines the expected source structure, transformations, and output contract. Templates can be implemented through:

• a sample-file query and a transformation function;
• a parameterised query that users copy and configure;
• a dataflow or shared semantic preparation layer;
• a documented M-code pattern stored in a controlled repository.

A good template specifies:

1. required input columns and data types;
2. optional columns and default behaviour;
3. accepted file extensions or source objects;
4. naming and date conventions;
5. error-handling rules;
6. output columns and their data types;
7. ownership and refresh expectations.

The output contract is particularly important. A downstream query should be able to rely on the function returning, for example, OrderID as text, OrderDate as date, Quantity as whole number, and SalesAmount as currency. Without such a contract, small source changes may produce unexpected model errors.

6. Worked Application: Regional Sales Files

Background

A distributor receives one CSV file per region each month. Files are placed in a shared folder. Each file should contain OrderID, OrderDate, ProductCode, Quantity, and SalesAmount, but staff occasionally insert blank rows, alter column order, or enter invalid quantities.

Problem

The BI team initially created a separate query for every region. Each query contained slightly different cleaning rules. When the organisation changed the sales amount format, four queries were updated correctly, two were missed, and regional totals no longer reconciled.

Analysis

The problem is architectural rather than merely technical. The solution contains repeated acquisition and cleaning logic, no central contract, and no systematic exception reporting. A reusable function can centralise the common transformation, while parameters and references can separate configuration from business logic.

Approach and Solution

The team creates:

• pFolderPath, a text parameter for the shared folder;
• src_SalesFiles, which lists and filters files;
• fnTransformSalesFile, which standardises each file;
• stg_Sales, which combines function results and applies validation;
• out_Sales, which references stg_Sales and selects model-ready columns;
• out_SalesExceptions, which references stg_Sales and filters invalid records.

The function uses a required-column check:

(fileContent as binary, fileName as text) as table =>
let
    Raw = Csv.Document(fileContent, [Delimiter = ",", Encoding = 65001]),
    Headers = Table.PromoteHeaders(Raw, [PromoteAllScalars = true]),
    Required = {"OrderID", "OrderDate", "ProductCode", "Quantity", "SalesAmount"},
    Existing = Table.ColumnNames(Headers),
    Missing = List.Difference(Required, Existing),
    Checked =
        if List.Count(Missing) > 0 then
            error "Missing columns in " & fileName & ": " & Text.Combine(Missing, ", ")
        else
            Headers,
    Typed = Table.TransformColumnTypes(
        Checked,
        {
            {"OrderID", type text},
            {"OrderDate", type date},
            {"ProductCode", type text},
            {"Quantity", Int64.Type},
            {"SalesAmount", Currency.Type}
        }
    ),
    AddedSource = Table.AddColumn(Typed, "SourceFile", each fileName, type text)
in
    AddedSource

The staging query then adds a validity indicator:

Table.AddColumn(
    Combined,
    "IsValid",
    each [Quantity] <> null and [Quantity] > 0
        and [SalesAmount] <> null and [SalesAmount] >= 0,
    type logical
)

The final sales query references the staging query and keeps valid rows. The exceptions query references the same staging query and keeps rows where IsValid = false.

Outcome

The team now maintains the file logic in one function. Adding a new region requires no new cleaning query; placing a correctly structured file in the folder is sufficient. Invalid records remain visible rather than being silently discarded. The model receives a consistent schema, and the exception output supports operational follow-up.

Learning Points

The case demonstrates four principles:

1. A function should express repeatable transformation logic.
2. A staging query should provide a stable, inspectable intermediate result.
3. References prevent competing versions of the same business rule.
4. Validation and exception handling are part of reusable design, not optional extras.

7. Performance and Maintainability

Reusable design does not automatically guarantee good performance. Important considerations include:

Query folding

For relational sources, query folding occurs when Power Query translates transformations into operations executed by the source system. Filtering early, selecting only required columns, and avoiding unnecessary custom row-by-row operations can improve performance. Not every transformation folds, and folding behaviour depends on the connector and source.

Avoiding repeated evaluation

A complex referenced query may be evaluated by several downstream queries. The exact evaluation and caching behaviour depends on the environment and operation. It is therefore unwise to assume that a reference always materialises a permanent intermediate table. Where performance is critical, consider performing common preparation in a dataflow, database view, or other shared layer.

Reducing data volume

Remove irrelevant columns and rows as early as is safe. However, do not remove audit fields such as source file name before they are used for traceability. Selective expansion is preferable to expanding every column from a nested table.

Stable schemas

Functions should handle foreseeable variations deliberately. MissingField.UseNull may be appropriate for optional columns, whereas silently ignoring a missing required column can produce incomplete analysis. Schema checks should distinguish acceptable variation from a genuine source failure.

8. Governance, Security, and Ethical Considerations

Reusable queries can spread errors quickly, so governance is essential. Teams should maintain:

• ownership for each shared query and function;
• version history and change documentation;
• test files representing normal and abnormal inputs;
• refresh monitoring and failure alerts;
• defined data-source credentials and privacy settings;
• documented retention and sensitivity requirements.

Power Query privacy levels help control how data from different sources may be combined. They should be configured in accordance with organisational policy rather than bypassed merely to remove a warning.

Parameters containing server names or paths are not substitutes for access control. Sensitive credentials, personal data, and confidential records must not be embedded in M code or exported in templates without protection. When a reusable function processes employee, customer, or financial data, the designer should apply data minimisation, appropriate access restrictions, and relevant legal requirements such as privacy and retention obligations.

A further ethical issue is silent correction. Converting invalid values to null may make a refresh succeed while concealing systematic data-entry problems. Transparent exception reporting is more responsible than silently removing inconvenient records.

9. Contemporary Practice and AI-Assisted Development

Modern Power BI projects increasingly use shared dataflows, deployment pipelines, source control, and automated validation. These practices complement reusable Power Query design. A function developed for a desktop report may later be moved into a centrally managed dataflow or reused by multiple semantic models.

AI assistants can help explain M code, propose transformations, or generate test cases. However, generated code must be reviewed for data types, privacy implications, folding behaviour, error handling, and business meaning. AI should assist implementation, not replace verification or ownership. In particular, a plausible-looking function may mishandle locale-specific dates, currency formats, nulls, or malformed files.

Best-Practice Checklist

Before publishing a reusable Power Query solution, confirm that:

• source, staging, transformation, and output responsibilities are clear;
• references are used where shared logic should have one definition;
• parameters have meaningful names, types, defaults, and documentation;
• functions specify input and output expectations;
• required columns and data types are validated;
• errors are visible through controlled handling or exception outputs;
• unnecessary rows and columns are removed at an appropriate stage;
• source-file or record-level lineage is retained where useful;
• privacy, credentials, and permissions are governed correctly;
• representative test files have been used;
• refresh performance and failure behaviour have been assessed.

Chapter Summary

Reusable Power Query design treats transformations as maintainable components. Staging queries standardise incoming data; referenced queries reuse a common result without copying logic; parameters separate configuration from transformation rules; and custom functions apply a defined operation to many inputs. The folder-combine pattern illustrates how these features support scalable ingestion of recurring files. Effective solutions also validate schemas, preserve lineage, expose exceptions, consider query folding and evaluation cost, and follow security and privacy requirements. Templates and shared layers extend these principles across reports and teams, while AI tools may support development only when their output is professionally reviewed.

Key Terms

Parameter: A named value that controls query behaviour without changing the transformation logic.

Query reference: A query that begins from the result of another query.

Query duplicate: An independent copy of a query's M code.

Staging query: An intermediate query that cleans and standardises source data.

Source query: A query that connects to an external data source.

Output query: A query prepared for loading into a Power BI model.

Custom function: A reusable M expression that accepts inputs and returns an output.

M language: The functional language used by Power Query.

Input contract: The documented type and structure expected by a function.

Output contract: The documented structure and data types returned by a function.

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

Schema: The names, types, and structural organisation of fields in a data set.

Data lineage: Information showing where data originated and how it was transformed.

Exception query: A query that isolates invalid, rejected, or suspicious records.

Template: A repeatable design containing expected inputs, transformations, and outputs.

Parameterisation: Replacing fixed values with configurable parameters.

Dependency: A relationship in which one query requires another query's result.

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

Privacy level: A setting governing how data sources may be combined.

Refresh: The process of rerunning queries to obtain current data.

Review Questions

Short Answer Questions

1. What is the principal difference between duplicating and referencing a query?
2. State two purposes of a staging query.
3. What is a parameter, and why is it useful in a Power BI solution?
4. What does a custom function return?
5. Why should a reusable function validate required columns?

Long Answer Questions

1. Explain how source, staging, reference, and output queries can be organised in a maintainable architecture.
2. Describe the folder-combine pattern and the role of a custom function within it.
3. Discuss how parameters improve portability while not replacing security controls.
4. Explain the benefits and risks of using try ... otherwise in reusable transformations.
5. Discuss how query folding, data reduction, and stable schemas affect performance and maintainability.

Analytical / Application Questions

1. A company has six regional queries containing nearly identical cleaning steps. Design a revised architecture using staging queries, references, and a custom function. Justify each component.
2. A monthly file is missing SalesAmount, but the refresh currently succeeds by silently creating nulls. Evaluate whether this is appropriate and propose a governance-oriented alternative.
3. Design a parameter and validation strategy for moving a Power BI solution from a development folder to a production folder while preserving auditability and privacy.

Multiple Choice Questions with Answers

Question 1

Which approach normally preserves a single shared definition of transformation logic?

A. Creating a duplicate query for every output

B. Creating a reference to a staging query

C. Copying applied steps into a new workbook

D. Changing the report visual filters

Correct Answer: B. Creating a reference to a staging query

Question 2

What is the primary purpose of a staging query?

A. To create dashboard visualisations

B. To store user credentials

C. To standardise and clean incoming data

D. To replace the semantic model

Correct Answer: C. To standardise and clean incoming data

Question 3

Which M construct is most appropriate for applying the same transformation to many files?

A. A custom function

B. A report bookmark

C. A calculated visual

D. A workspace role

Correct Answer: A. A custom function

Question 4

What does Folder.Files(pFolderPath) require in this chapter's pattern?

A. A numeric parameter

B. A text folder-path parameter

C. A Boolean output

D. A table of dates

Correct Answer: B. A text folder-path parameter

Question 5

Why should a reusable function have an output contract?

A. To guarantee that every source file has identical names before import

B. To define the expected returned structure and data types

C. To disable query dependencies

D. To eliminate the need for testing

Correct Answer: B. To define the expected returned structure and data types

Question 6

What is a responsible use of an exceptions query?

A. To hide failed records from business users

B. To isolate and investigate invalid records

C. To replace all source validation

D. To store passwords used by the connector

Correct Answer: B. To isolate and investigate invalid records

Question 7

Which practice can improve performance when it is safe and supported by the connector?

A. Keeping every source column until the final step

B. Filtering and selecting relevant data early

C. Duplicating every staging query

D. Converting all values to text immediately

Correct Answer: B. Filtering and selecting relevant data early

Question 8

What does query folding describe?

A. Compressing a Power BI file into a ZIP archive

B. Translating Power Query operations into source-system operations

C. Combining unrelated reports into one workspace

D. Encrypting parameter values

Correct Answer: B. Translating Power Query operations into source-system operations

Question 9

Which statement about parameters is correct?

A. They automatically provide access control

B. They allow configurable values such as paths or dates

C. They can contain only Boolean values

D. They eliminate the need for credentials

Correct Answer: B. They allow configurable values such as paths or dates

Question 10

What is a potential risk of using try ... otherwise null indiscriminately?

A. It may conceal data-quality problems

B. It always prevents query folding

C. It converts tables into visualisations

D. It removes all query dependencies

Correct Answer: A. It may conceal data-quality problems