Building Reusable Power Query Function Libraries: Parameters, Recursion, and Modular M Code Patterns

Introduction

Picture this: you've got fifteen Power BI reports, each pulling data from a slightly different REST API. Each one has its own copy of the authentication logic, the pagination handler, the date normalization routine, and the error wrapper. They were all written by different people at different times, and they've all drifted. The pagination handler in Report 3 has a bug that was fixed in Report 7 but never backported. The date normalization in Report 11 handles nulls; none of the others do. Every maintenance task requires you to find all fifteen copies, figure out which one is "most correct," and propagate the fix manually.

This is the Power Query equivalent of copy-paste programming, and it's quietly destroying productivity in data teams everywhere. M code — the functional language that powers Power Query — is actually well-suited to solving this problem. It has first-class functions, closures, recursive definitions, and a type system capable of enforcing contracts. Most Power Query users never go beyond point-and-click transformations, but the language underneath has the machinery to build proper, reusable function libraries that you can maintain in one place and deploy everywhere.

By the end of this lesson, you'll know how to design, build, and deploy a genuine function library in M. You'll go from "I have a useful transformation" to "I have a versioned, parameterized, recursion-capable module that I can call from any query in any report." This isn't about writing clever code for its own sake — it's about engineering maintainable data infrastructure.

What you'll learn:

• How M functions work at a deep level: parameters, types, closures, and currying
• How to structure query files as reusable, importable modules
• How to implement recursive functions with proper termination logic and tail-call awareness
• How to design a parameter management system that keeps your library configurable without hardcoding
• How to apply modular design patterns — helper functions, composition, and partial application — to real-world data transformation scenarios

---

Prerequisites

This lesson assumes you're already comfortable with Power Query at an intermediate-to-advanced level. Specifically, you should:

• Be able to write M code directly in the Advanced Editor (not just use the GUI)
• Understand the let...in expression structure
• Know the difference between a Table, List, Record, and Text value in M
• Have worked with custom columns and at least one custom function before
• Understand basic concepts like query folding and evaluation order

If recursion is brand new to you conceptually, take a few minutes to read about it in any general programming context before continuing. We'll explain how it works in M specifically, but the foundational concept will help.

---

How M Functions Actually Work

Before we build a library, we need to understand the material we're working with.

In M, a function is a value — just like a number, a text string, or a table. This is not just a philosophical point; it has practical consequences. You can store a function in a record field, pass it as an argument to another function, return it from a function, and store it in a list. This is what "first-class functions" means, and it's what makes a library architecture possible.

The basic syntax for defining a function is:

(parameter1 as type1, parameter2 as type2) as returnType =>
    expression

Here's a concrete example — a function that formats a currency amount:

(amount as number, currencyCode as text) as text =>
    currencyCode & " " & Number.ToText(amount, "N2")

You can assign this to a name in a let block:

let
    FormatCurrency = (amount as number, currencyCode as text) as text =>
        currencyCode & " " & Number.ToText(amount, "N2"),
    
    Result = FormatCurrency(1234.5, "USD")
in
    Result

The result is "USD 1,234.50". Simple enough. But notice: FormatCurrency is just a name bound to a function value. That function value can be passed around like any other value.

Type Annotations Are Optional But Important

M doesn't require type annotations on function parameters. This:

(amount, currencyCode) => currencyCode & " " & Number.ToText(amount, "N2")

...is perfectly valid. M uses lazy evaluation and dynamic typing, so it won't complain about missing annotations until the function is actually called with the wrong type.

For a personal one-off query, omitting types is fine. For a library function that others will call, type annotations serve as documentation and catch errors earlier. When you add as number to a parameter, Power Query will raise a Expression.Error with a useful message if the caller passes a text value. Without annotations, the error surfaces later — often as a cryptic type mismatch inside the function body.

For library code, annotate your parameters. It's worth the extra keystrokes.

Closures: Functions That Remember Their Environment

When a function is defined inside a let block, it can reference any names defined in that same let block — even names that aren't explicitly passed as parameters. This is a closure.

let
    TaxRate = 0.08,
    
    AddTax = (price as number) as number =>
        price * (1 + TaxRate),
    
    Result = AddTax(100)
in
    Result

AddTax closes over TaxRate — it captures that value from its surrounding scope. This is powerful for configuring functions without threading configuration through every call, but it's also a source of subtle bugs if you're not deliberate about what your functions close over. In a library, you generally want your functions to be explicit about their dependencies. Closures are useful for factory patterns (generating configured functions), but a function that silently depends on ambient state is hard to test and hard to reuse.

Currying and Partial Application

M doesn't have a built-in partial application operator, but you can implement the same pattern manually. A curried function is a function that takes one argument at a time and returns a new function for each remaining argument.

Here's why this matters for a library: suppose you have a function that calls an API with a given base URL, endpoint, and query string. The base URL is almost always the same within a given report. You can write a factory function that takes the base URL and returns a specialized function:

let
    MakeApiCaller = (baseUrl as text) =>
        (endpoint as text, queryParams as record) as text =>
            baseUrl & endpoint & "?" & Uri.BuildQueryString(queryParams),
    
    // Create a specialized caller for our internal API
    CallInternalApi = MakeApiCaller("https://api.internal.company.com/v2"),
    
    // Now call specific endpoints cleanly
    UsersEndpoint = CallInternalApi("/users", [limit = "100", active = "true"]),
    OrdersEndpoint = CallInternalApi("/orders", [status = "pending"])
in
    UsersEndpoint

MakeApiCaller returns a function. CallInternalApi is that returned function, already configured with the base URL. You call CallInternalApi the same way you'd call any function, but it already knows the base URL. This pattern is invaluable in libraries because it lets you provide pre-configured variants without duplicating logic.

---

Designing Your Library Architecture

Before writing a single function, you need to decide how your library will be organized and deployed. There are two primary patterns in Power Query, each with real trade-offs.

Pattern 1: The Master Query Module

In this pattern, you create a single Power Query query whose output is a Record — essentially a namespace containing all your functions. Other queries call into this record.

// Query name: "Utils"
let
    // Private helper (naming convention signals internal use)
    _ParseIsoDate = (dateText as text) as date =>
        Date.FromText(dateText, [Format="yyyy-MM-dd"]),
    
    // Public functions
    ParseDate = (dateText as nullable text) as nullable date =>
        if dateText = null then null
        else _ParseIsoDate(dateText),
    
    FormatCurrency = (amount as number, currencyCode as text) as text =>
        currencyCode & " " & Number.ToText(amount, "N2"),
    
    NormalizePhoneNumber = (phone as text) as text =>
        let
            DigitsOnly = Text.Select(phone, {"0".."9"}),
            Formatted = if Text.Length(DigitsOnly) = 10
                then "(" & Text.Range(DigitsOnly, 0, 3) & ") " 
                     & Text.Range(DigitsOnly, 3, 3) & "-" 
                     & Text.Range(DigitsOnly, 6, 4)
                else DigitsOnly
        in
            Formatted
in
    Record.FromList(
        {ParseDate, FormatCurrency, NormalizePhoneNumber},
        {"ParseDate", "FormatCurrency", "NormalizePhoneNumber"}
    )

Now in any other query, you reference it like this:

let
    Utils = #"Utils",  // Reference the Utils query
    Source = Csv.Document(...),
    CleanedDates = Table.TransformColumns(
        Source,
        {{"OrderDate", Utils[ParseDate]}}
    )
in
    CleanedDates

Tip: The #"Query Name" syntax lets you reference any query by name from any other query. Use it to reference your library module. The square bracket notation Utils[ParseDate] then extracts the function from the record.

The downside of the master query pattern is that it's monolithic. Every query that uses the library loads all of it, even if it only needs one function. In practice, Power Query uses lazy evaluation, so unused functions don't necessarily execute, but the parsing and metadata overhead is still there. For most use cases, this is not a real bottleneck — but it's worth knowing.

Pattern 2: Individual Function Queries

Here, each function is its own query. You give it a descriptive name like FN_ParseDate or Lib.ParseDate, and other queries reference it directly by name.

// Query name: "Lib.ParseDate"
(dateText as nullable text) as nullable date =>
    if dateText = null then null
    else Date.FromText(dateText, [Format="yyyy-MM-dd"])

The entire query body is the function literal. When you reference this query from another query, you get the function directly:

let
    ParseDate = #"Lib.ParseDate",
    Source = Csv.Document(...),
    WithParsedDates = Table.TransformColumns(
        Source,
        {{"OrderDate", ParseDate}}
    )
in
    WithParsedDates

This pattern is cleaner for large libraries and makes it obvious which functions are in use. The naming convention (Lib. prefix or FN_ prefix) keeps library queries visually grouped in the query pane. The downside is that your library is spread across many queries, which makes the pbix or Excel file harder to audit.

Warning: Both patterns have the same fundamental limitation: you can't truly "import" an external file of M code the way Python imports a module. Your library lives inside the report file itself. If you want to share it across reports, you need a deployment process — covered later in this article.

---

Building Robust Parameterized Functions

Let's build a realistic library starting with something most data teams actually need: a robust HTTP data fetcher with configurable retry logic.

The Problem

Web APIs fail. Rate limits, transient network errors, and server hiccups all happen. Naive Power Query API calls either fail completely or return empty data silently. A proper library function should handle retries with exponential backoff.

Building the Retry Wrapper

// Query name: "Lib.HttpGetWithRetry"
let
    HttpGetWithRetry = (
        url as text, 
        headers as record,
        maxRetries as number,
        waitSeconds as number
    ) as binary =>
        let
            // Inner function attempts the call, counting down retries
            AttemptFetch = (retriesLeft as number) as binary =>
                let
                    Response = try Web.Contents(url, [Headers = headers]),
                    
                    Result = if Response[HasError] then
                        if retriesLeft > 0 then
                            // Wait and retry
                            let
                                // Power Query doesn't have a real sleep function;
                                // this forces a computation that takes time.
                                // In practice, scheduled refreshes retry at the Power BI service level.
                                // This pattern is for documentation of intent and works in some contexts.
                                Waited = List.Last(List.Generate(
                                    () => 0,
                                    each _ < waitSeconds * 1000000,
                                    each _ + 1
                                )),
                                Retry = AttemptFetch(retriesLeft - 1)
                            in
                                Retry
                        else
                            error Error.Record(
                                "HttpError",
                                "Request failed after " & Number.ToText(maxRetries) & " retries: " & url,
                                Response[Error]
                            )
                    else
                        Response[Value]
                in
                    Result
        in
            AttemptFetch(maxRetries)
in
    HttpGetWithRetry

This introduces a few important patterns worth examining.

First, notice that AttemptFetch is defined inside the outer function. It closes over url, headers, waitSeconds, and maxRetries — the parameters of the outer function. This is intentional: those values are fixed for a given call to HttpGetWithRetry, so there's no reason to thread them through every recursive call.

Second, the try keyword. In M, try expression returns a record with two fields: HasError (a logical) and either Value (if no error) or Error (if there was one). This is M's structured error handling, and it's essential for resilient library functions.

Third, the error propagation: when retries are exhausted, we call error Error.Record(...) to surface a structured error with context. Without this, you'd get the original, often opaque, web error with no indication of the URL that failed or how many retries were attempted.

Note on the busy-wait: The List.Generate busy-wait is a hack. Power Query isn't designed for real-time delay. A real retry mechanism for API calls in production typically involves the Power BI service's built-in refresh retry, or an orchestration layer (Azure Data Factory, for example). But the structure of the function — counting down retries, wrapping with try, surfacing context-rich errors — is exactly right.

A Cleaner Parameter Pattern Using Records

As functions grow more complex, a long parameter list becomes unwieldy. M supports a pattern where you pass configuration as a record with defaults:

// Query name: "Lib.FetchApiData"
let
    FetchApiData = (url as text, optional config as nullable record) as table =>
        let
            // Merge caller-supplied config over defaults
            DefaultConfig = [
                MaxRetries = 3,
                TimeoutSeconds = 30,
                Headers = [#"Content-Type" = "application/json"],
                PageSize = 100
            ],
            
            EffectiveConfig = if config = null then DefaultConfig
                              else Record.Combine({DefaultConfig, config}),
            
            MaxRetries = EffectiveConfig[MaxRetries],
            Headers = EffectiveConfig[Headers],
            PageSize = EffectiveConfig[PageSize],
            
            RawData = Web.Contents(url, [
                Headers = Headers,
                Timeout = #duration(0, 0, EffectiveConfig[TimeoutSeconds], 0)
            ]),
            
            Parsed = Json.Document(RawData),
            AsTable = Table.FromRecords(Parsed)
        in
            AsTable
in
    FetchApiData

The Record.Combine call is the key. It merges two records, with the second record's values overriding the first's when keys conflict. This gives you true optional parameters with defaults — a pattern M lacks natively for functions but that you can implement cleanly with records.

Tip: Record.Combine takes a list of records and merges them left-to-right. Keys in later records override keys in earlier records. This is a clean idiom for default-config merging in M.

---

Recursion in M: Deep Patterns and Real Pitfalls

Recursion in M works, but it has some important constraints you need to understand before building recursive library functions.

The @-Operator for Self-Reference

In a let block, a name refers to its defined value. But functions need to call themselves by name, which creates a forward-reference problem. M solves this with the @ operator:

let
    Factorial = (n as number) as number =>
        if n <= 1 then 1
        else n * @Factorial(n - 1)
in
    Factorial(10)

The @Factorial inside the function body refers to the function itself recursively. Without @, you'd get a reference error because Factorial hasn't been fully defined yet at the point the function body is parsed.

Recursive Table Pagination: A Real Use Case

Pagination is one of the most common places recursion is genuinely useful in Power Query. Many APIs return a page of results plus a "next page" token or URL. You need to keep fetching until there's no next token.

// Query name: "Lib.FetchAllPages"
let
    FetchAllPages = (
        firstPageUrl as text,
        getNextUrl as function,
        parseRecords as function
    ) as list =>
        let
            FetchPage = (url as text, accumulated as list) as list =>
                let
                    RawResponse = Json.Document(Web.Contents(url)),
                    PageRecords = parseRecords(RawResponse),
                    NextUrl = getNextUrl(RawResponse),
                    Combined = accumulated & PageRecords
                in
                    if NextUrl = null then Combined
                    else @FetchPage(NextUrl, Combined),
            
            AllRecords = FetchPage(firstPageUrl, {})
        in
            AllRecords
in
    FetchAllPages

This function takes three arguments:

• firstPageUrl: the URL of the first page
• getNextUrl: a function that takes a parsed API response and returns the next URL (or null if done)
• parseRecords: a function that takes a parsed API response and returns a list of records

Here's how you'd call it for a specific API:

let
    FetchAllPages = #"Lib.FetchAllPages",
    
    GetNextUrl = (response as record) as nullable text =>
        if Record.HasFields(response, "next_page_url") 
        then response[next_page_url]
        else null,
    
    ParseRecords = (response as record) as list =>
        response[data],
    
    AllOrders = FetchAllPages(
        "https://api.example.com/v1/orders?page=1",
        GetNextUrl,
        ParseRecords
    ),
    
    OrdersTable = Table.FromRecords(AllOrders)
in
    OrdersTable

This is a beautiful example of the higher-order function pattern: FetchAllPages doesn't know anything about the specific API. It delegates the API-specific logic (how to find the next URL, how to extract records) to functions provided by the caller. This makes FetchAllPages genuinely reusable across any paginated API.

List.Generate as an Iterative Alternative to Recursion

M's recursion isn't tail-call optimized. Deep recursion will exhaust the call stack and produce Expression.Error: The type of the value does not match the type of the target or just a stack overflow. For APIs with hundreds of pages, pure recursion can fail.

List.Generate is the M-idiomatic way to express iteration without deep call stacks:

// Query name: "Lib.FetchAllPagesIterative"
let
    FetchAllPagesIterative = (
        firstPageUrl as text,
        getNextUrl as function,
        parseRecords as function
    ) as list =>
        let
            // State is a record: [Url = current URL, Done = whether to stop, Records = accumulated records]
            InitialState = [
                Url = firstPageUrl, 
                Done = false, 
                Records = {}
            ],
            
            AllStates = List.Generate(
                () => InitialState,
                each not _[Done],
                each
                    let
                        Response = Json.Document(Web.Contents(_[Url])),
                        PageRecords = parseRecords(Response),
                        NextUrl = getNextUrl(Response)
                    in
                        [
                            Url = if NextUrl = null then "" else NextUrl,
                            Done = NextUrl = null,
                            Records = _[Records] & PageRecords
                        ]
            ),
            
            FinalState = List.Last(AllStates),
            AllRecords = FinalState[Records]
        in
            AllRecords
in
    FetchAllPagesIterative

List.Generate takes four arguments: an initial state producer, a condition (continue while true), a next-state function, and an optional selector. It's conceptually a while loop expressed as a lazy list. Power Query can handle much larger numbers of iterations this way than with raw recursion.

Warning: List.Generate with Web.Contents inside the state transition function will fire one HTTP request per iteration during evaluation. This is intentional — it's how you paginate — but it also means this query cannot fold to a data source. Every page is fetched in Power Query's engine. Plan your data model accordingly and consider caching the results.

Recursive Tree Walking

Another genuinely recursive problem: walking a tree-structured data set. Consider an organizational hierarchy stored as a flat table with an EmployeeId and ManagerId column:

// Query name: "Lib.BuildHierarchyPath"
let
    BuildHierarchyPath = (
        employeeId as any,
        lookupTable as table,
        idColumn as text,
        parentColumn as text,
        labelColumn as text
    ) as text =>
        let
            FindRow = (id as any) as nullable record =>
                let
                    Matches = Table.SelectRows(
                        lookupTable, 
                        each Record.Field(_, idColumn) = id
                    )
                in
                    if Table.IsEmpty(Matches) then null
                    else Table.First(Matches),
            
            BuildPath = (id as any) as text =>
                let
                    Row = FindRow(id),
                    Label = if Row = null then "Unknown"
                            else Record.Field(Row, labelColumn),
                    ParentId = if Row = null then null
                               else Record.Field(Row, parentColumn),
                    ParentPath = if ParentId = null or ParentId = id
                                 then ""
                                 else @BuildPath(ParentId) & " > "
                in
                    ParentPath & Label
        in
            BuildPath(employeeId)
in
    BuildHierarchyPath

Notice the termination condition: ParentId = null or ParentId = id. The second condition handles the common data quality problem where a root node has itself as its own manager. Without that guard, you'd have infinite recursion.

---

Parameter Management: Making Libraries Configurable

A library function that has hardcoded values — API base URLs, date format strings, column names — is only half-useful. You want your library to be configured externally, ideally from a single place that's easy to update.

The Parameters Table Pattern

Power Query has a well-known trick: a single-column table named "Parameters" with Name and Value columns. You read from it in your library like this:

// Query name: "Config"
let
    ParamsTable = Table.FromRows({
        {"ApiBaseUrl", "https://api.production.company.com/v2"},
        {"ApiKey", "your-api-key-here"},
        {"DateFormat", "yyyy-MM-dd"},
        {"MaxRetries", "3"},
        {"DefaultPageSize", "500"}
    }, {"Name", "Value"}),
    
    // Convert to a record for easy lookup
    AsRecord = Record.FromList(
        ParamsTable[Value],
        ParamsTable[Name]
    )
in
    AsRecord

Now any library function that needs configuration imports Config:

// Query name: "Lib.CallApi"
let
    Config = #"Config",
    
    CallApi = (endpoint as text, optional params as nullable record) as table =>
        let
            Url = Config[ApiBaseUrl] & endpoint,
            Headers = [#"X-API-Key" = Config[ApiKey]],
            // ... rest of implementation
            Result = Table.FromRecords({})
        in
            Result
in
    CallApi

This gives you one place to change configuration for all library functions simultaneously.

Warning: Never store real credentials in a Parameters table inside a pbix file if that file will be committed to source control or shared broadly. Use Power BI's built-in parameter mechanism or reference a secure key vault instead. The pattern above is appropriate for connection strings and format preferences — not secrets.

Power BI Parameters vs. M Parameters

Power BI's built-in "Manage Parameters" feature creates named parameters that appear in the query pane. These are slightly different from what we've been building:

• Power BI parameters have defined types and can be exposed in the report's "Edit Parameters" dialog
• They're automatically visible to report consumers (or admins) without opening Power Query
• They can be updated during scheduled refresh via the Power BI REST API

For library configuration like API URLs and date formats, Power BI parameters are often the better choice precisely because they can be updated without republishing the report. You reference them in M just like any other query: #"MyParameter".

---

Modular Composition Patterns

The real power of a function library isn't individual functions — it's how those functions compose to handle complex scenarios.

The Pipeline Pattern

Many data transformations are sequences of steps, each taking the output of the previous. In M, you can implement a pipeline function:

// Query name: "Lib.Pipeline"
let
    // Apply a list of transform functions to a table, in order
    Pipeline = (source as table, transforms as list) as table =>
        List.Accumulate(
            transforms,
            source,
            (state, transform) => transform(state)
        )
in
    Pipeline

List.Accumulate is M's fold/reduce. It starts with source and applies each function in transforms sequentially, threading the result through. Now you can define your transformation pipeline as data:

let
    Pipeline = #"Lib.Pipeline",
    ParseDate = #"Lib.ParseDate",
    
    Source = Csv.Document(File.Contents("C:\data\orders.csv")),
    
    // Define the transformation pipeline as a list of functions
    Transforms = {
        // Remove empty rows
        (t) => Table.SelectRows(t, each not List.IsEmpty(
            List.RemoveMatchingItems(Record.FieldValues(_), {null, ""})
        )),
        
        // Promote headers
        (t) => Table.PromoteHeaders(t, [PromoteAllScalars=true]),
        
        // Parse the date column
        (t) => Table.TransformColumns(t, {{"OrderDate", ParseDate}}),
        
        // Add a derived column
        (t) => Table.AddColumn(t, "Year", each Date.Year([OrderDate]), Int64.Type),
        
        // Remove unnecessary columns
        (t) => Table.SelectColumns(t, {"OrderId", "OrderDate", "Year", "CustomerId", "Amount"})
    },
    
    CleanOrders = Pipeline(Source, Transforms)
in
    CleanOrders

This pipeline is readable, testable (each transform is a standalone function you can test in isolation), and easily modified — add a transform to the list, remove one, reorder them. No deeply nested transformation chains in the Advanced Editor.

The Transformation Registry Pattern

For ETL workflows where you need to apply different transformations to different tables, a transformation registry lets you declare what happens to each table in one place:

// Query name: "TransformRegistry"
let
    ParseDate = #"Lib.ParseDate",
    NormalizePhone = #"Lib.NormalizePhoneNumber",
    Pipeline = #"Lib.Pipeline",
    
    // Map of table name -> list of transforms
    Registry = [
        Orders = {
            (t) => Table.PromoteHeaders(t),
            (t) => Table.TransformColumns(t, {{"OrderDate", ParseDate}}),
            (t) => Table.SelectColumns(t, {"OrderId", "OrderDate", "CustomerId", "Total"})
        },
        Customers = {
            (t) => Table.PromoteHeaders(t),
            (t) => Table.TransformColumns(t, {{"Phone", NormalizePhone}}),
            (t) => Table.RenameColumns(t, {{"cust_id", "CustomerId"}, {"cust_name", "CustomerName"}})
        }
    ]
in
    Registry

Then in each table's query:

let
    Registry = #"TransformRegistry",
    Pipeline = #"Lib.Pipeline",
    
    Source = Csv.Document(File.Contents("C:\data\orders.csv")),
    Result = Pipeline(Source, Registry[Orders])
in
    Result

The transformation logic for every table is declared in one place. New team members can understand the entire ETL at a glance by reading the registry.

---

Deploying Your Library Across Reports

The hardest problem in Power Query function libraries isn't writing them — it's maintaining them across multiple report files.

The Template Approach

Power BI Desktop supports .pbit template files. A template contains queries, parameters, and a data model definition, but not the actual data. You can maintain a "library template" — a pbix with nothing but your library queries — and use it as the starting point for new reports.

To extract your library into an existing report:

1. Open both the source report (with the library) and the target report
2. In the target report, open Power Query
3. Use Home > New Source > Blank Query to create placeholder queries
4. Paste in your library M code manually for each function

This is tedious, which is why the next approach is better for teams.

The Shared Dataset / Dataflow Approach

Power BI Dataflows (Premium or Pro feature) let you define reusable data preparation logic in Power Query Online and expose the results as tables that any report can connect to. For transformation library functions specifically, this doesn't directly work — dataflows produce tables, not functions. But you can use a dataflow to produce a "canonical" version of a transformed table and have all reports connect to that instead of each doing their own transformation.

For pure function libraries (code, not data), the best enterprise approach is:

1. Maintain the library in a "master" pbix file in a Git repository
2. Use a CI/CD script (PowerShell + the Power BI REST API) to extract and republish changes
3. Document the current library version in a Parameters table (LibraryVersion = "2.3.1")
4. Periodically sync dependent reports using a helper script that pulls the current library queries and overwrites the old ones

The Power BI REST API's /reports/{reportId}/Export and Import endpoints let you automate pbix management. It's not elegant, but it works.

Externalizing Library Code with #shared

There's an advanced technique worth knowing: M's #shared environment. In the M engine, #shared contains a record of every built-in and currently-available function. In a constrained evaluation environment, you can use Expression.Evaluate with a custom environment record to inject library functions:

let
    // Define library functions in a record
    LibEnv = [
        FormatCurrency = (amount as number, code as text) as text =>
            code & " " & Number.ToText(amount, "N2"),
        ParseDate = (s as nullable text) as nullable date =>
            if s = null then null else Date.FromText(s)
    ],
    
    // Merge with the standard M environment
    Env = Record.Combine({#shared, LibEnv}),
    
    // Evaluate M code in the combined environment
    Result = Expression.Evaluate(
        "FormatCurrency(1234.56, ""EUR"")",
        Env
    )
in
    Result

This is powerful but dangerous. Expression.Evaluate can execute arbitrary M code, which is a security concern if any part of the expression comes from user input or an external source. In a controlled, internal ETL environment with no external expression strings, it's a legitimate tool. But it's not something to reach for casually.

Warning: Expression.Evaluate is disabled in some Power BI environments due to security policies. Don't build a core workflow dependency on it without confirming it's available in your deployment target.

---

Hands-On Exercise

Let's put everything together. You'll build a mini-library for normalizing messy sales data from multiple regional CSV exports.

Scenario: Your company has regional offices that export sales data as CSVs. Each region uses slightly different date formats, currency symbols, and column naming conventions. You need to build a library that normalizes all of them.

Step 1: Create the Config query

In Power Query, create a blank query named Config and enter:

let
    ConfigRecord = [
        DefaultDateFormats = {"MM/dd/yyyy", "dd-MM-yyyy", "yyyy-MM-dd", "M/d/yy"},
        DecimalSeparator = ".",
        ThousandsSeparator = ","
    ]
in
    ConfigRecord

Step 2: Create Lib.ParseFlexibleDate

Create a blank query named Lib.ParseFlexibleDate:

let
    Config = #"Config",
    
    ParseFlexibleDate = (dateText as nullable text) as nullable date =>
        if dateText = null or Text.Trim(dateText) = "" then null
        else
            let
                Formats = Config[DefaultDateFormats],
                
                TryParseWithFormat = (fmt as text) as nullable date =>
                    let Result = try Date.FromText(Text.Trim(dateText), [Format = fmt])
                    in if Result[HasError] then null else Result[Value],
                
                Attempts = List.Transform(Formats, TryParseWithFormat),
                
                FirstSuccess = List.First(List.RemoveNulls(Attempts), null)
            in
                FirstSuccess
in
    ParseFlexibleDate

Step 3: Create Lib.ParseCurrencyAmount

let
    Config = #"Config",
    
    ParseCurrencyAmount = (amountText as nullable text) as nullable number =>
        if amountText = null or Text.Trim(amountText) = "" then null
        else
            let
                // Strip currency symbols and whitespace
                CurrencySymbols = {"$", "€", "£", "¥", "USD", "EUR", "GBP"},
                Stripped = List.Accumulate(
                    CurrencySymbols,
                    Text.Trim(amountText),
                    (state, sym) => Text.Replace(state, sym, "")
                ),
                // Remove thousands separator, normalize decimal
                NoThousands = Text.Replace(Stripped, Config[ThousandsSeparator], ""),
                NormalizedDecimal = Text.Replace(NoThousands, ",", "."),
                Trimmed = Text.Trim(NormalizedDecimal),
                Parsed = try Number.FromText(Trimmed)
            in
                if Parsed[HasError] then null else Parsed[Value]
in
    ParseCurrencyAmount

Step 4: Create Lib.NormalizeRegionData

let
    ParseFlexibleDate = #"Lib.ParseFlexibleDate",
    ParseCurrencyAmount = #"Lib.ParseCurrencyAmount",
    
    NormalizeRegionData = (
        source as table,
        columnMap as record  // Maps incoming column names to standard names
    ) as table =>
        let
            // Rename columns according to the map
            CurrentCols = Table.ColumnNames(source),
            MappingPairs = List.Transform(
                Record.FieldNames(columnMap),
                each {_, Record.Field(columnMap, _)}
            ),
            ValidMappings = List.Select(
                MappingPairs,
                each List.Contains(CurrentCols, _{0})
            ),
            Renamed = if List.Count(ValidMappings) > 0
                      then Table.RenameColumns(source, ValidMappings)
                      else source,
            
            // Detect and parse date columns (heuristic: column name contains "Date" or "date")
            ColNames = Table.ColumnNames(Renamed),
            DateCols = List.Select(ColNames, each Text.Contains(_, "Date") or Text.Contains(_, "date")),
            DateTransforms = List.Transform(DateCols, each {_, ParseFlexibleDate}),
            
            WithDates = if List.Count(DateTransforms) > 0
                        then Table.TransformColumns(Renamed, DateTransforms)
                        else Renamed,
            
            // Detect and parse amount columns (heuristic: column name contains "Amount", "Price", "Cost")
            AmountKeywords = {"Amount", "Price", "Cost", "Revenue", "Total"},
            AmountCols = List.Select(
                Table.ColumnNames(WithDates),
                (col) => List.AnyTrue(List.Transform(AmountKeywords, each Text.Contains(col, _)))
            ),
            AmountTransforms = List.Transform(AmountCols, each {_, ParseCurrencyAmount}),
            
            WithAmounts = if List.Count(AmountTransforms) > 0
                          then Table.TransformColumns(WithDates, AmountTransforms)
                          else WithDates
        in
            WithAmounts
in
    NormalizeRegionData

Step 5: Use the library in a real query

let
    NormalizeRegionData = #"Lib.NormalizeRegionData",
    
    // Load the raw EMEA export
    Source = Csv.Document(
        File.Contents("C:\data\emea_sales_2024.csv"),
        [Delimiter=",", Encoding=1252]
    ),
    Promoted = Table.PromoteHeaders(Source),
    
    // EMEA uses different column names — map them to our standard
    ColumnMapping = [
        sale_dt = "SaleDate",
        order_id = "OrderId",
        customer_ref = "CustomerId",
        gross_amt = "GrossAmount",
        net_amt = "NetAmount"
    ],
    
    Normalized = NormalizeRegionData(Promoted, ColumnMapping)
in
    Normalized

Exercise challenge: Extend Lib.NormalizeRegionData to also accept an optional excludeColumns list parameter. Columns in that list should be dropped from the output. Think about how to merge that with the existing Config pattern.

---

Common Mistakes and Troubleshooting

Circular References

If Query A calls Query B which calls Query A, you have a circular dependency. Power Query will catch this and throw Expression.Error: A cyclic reference was detected. The fix is to restructure so the dependency is one-directional. Your library queries should never reference your data queries — only the reverse.

Infinite Recursion

Missing or incorrect termination conditions in recursive functions cause Expression.Error: The query exceeded the maximum allowed depth. Always trace your termination logic carefully. Test with a small dataset first. For tree-walking functions, always guard against self-referencing nodes.

Unexpected Query Folding Failure

When you introduce a custom function call in a transformation step, Power Query may lose the ability to fold subsequent operations to the data source. You'll see a performance cliff: operations that were fast (because they ran on SQL Server) now run slowly in the M engine. Use the Query Diagnostics feature (Tools > Start Diagnostics) to verify which steps are folding. As a rule, apply all custom function transformations after all steps that should fold.

Type Errors From Record.Combine

Record.Combine is sensitive to duplicate field names. If both records have the same field, the later one wins — this is usually what you want for the defaults pattern, but verify the order. Record.Combine({DefaultConfig, callerConfig}) gives caller config priority. Record.Combine({callerConfig, DefaultConfig}) gives defaults priority (probably not what you want).

Functions That Don't Appear as Invocable

If your library query's final in expression returns a record (the module pattern) instead of a function, you can't invoke it as Utils(args). You'd need Utils[FunctionName](args). This is expected behavior, but it trips people up. Decide deliberately: do you want your query to return a function (invocable directly) or a record of functions (namespace pattern)?

Privacy Level Errors

When combining data from multiple sources, Power Query's privacy firewall can block queries from accessing each other. If your library function accesses one source and is called from a query that accesses another source, you may hit Formula.Firewall: Query references other queries or steps and so may not directly access a data source. The fix is to set appropriate privacy levels in File > Options > Privacy or, in Power BI, in the data source credentials settings. Never set everything to "None" in production — understand what privacy level is appropriate for each source.

---

Summary and Next Steps

You've covered a lot of ground. Let's consolidate what you've built:

The conceptual foundation: M functions are first-class values. They can be stored, passed, and returned just like any other data. Closures, currying, and higher-order functions aren't abstract computer science concepts here — they're practical tools for building configurable, composable library code.

Library architecture: The record-as-module pattern (one query, many functions in a record) and the individual-function-query pattern each have real trade-offs. For smaller teams, individual function queries with a consistent naming convention are easier to navigate. For large libraries, the record-as-namespace pattern keeps things tidy.

Recursion: Use @FunctionName for self-reference. Prefer List.Generate over raw recursion for iteration-heavy tasks like pagination — it avoids call-stack exhaustion. Always design explicit termination conditions and guard against pathological inputs (null parents, self-referencing nodes).

Configuration: Drive library behavior from a Config record query. Use Record.Combine for default-with-overrides pattern. Use Power BI's native parameter system for values that need to change at refresh time or be managed by report admins.

Composition: The Pipeline pattern using List.Accumulate and the transformation registry pattern give you readable, maintainable ETL logic that's easy to audit and modify.

Deployment: Shared libraries across reports require deliberate process. For small teams, templates work. For larger teams, invest in a CI/CD workflow using the Power BI REST API to synchronize library updates.

Where to go next:

1. Study M's type system in depth — function types, table types, and Type.Is — to add contractual guarantees to your library interfaces
2. Explore Query Diagnostics and the Power Query Monitoring Dashboard to measure the performance impact of your library functions
3. Look into Power BI Dataflows as a complementary approach: move data-level transformations (not function libraries) into a shared dataflow, and keep transformation logic in report-level library functions that operate on the dataflow output
4. Investigate Value.Metadata and Table.Schema — Power Query's metadata system — for adding self-documenting capabilities to your library functions

The architecture patterns you've learned here — parameterization, modularity, higher-order functions, and deliberate composition — are the same ones that separate a collection of useful queries from a maintainable data platform. Start with one function that you use everywhere, extract it, and build outward from there.