Writing Custom M Functions from Scratch in Power Query

When you're working with complex data transformations in Power Query, you'll inevitably hit a wall where the built-in functions just don't cut it. Maybe you need to parse custom date formats that your organization uses, or apply complex business logic that requires multiple steps repeated across different queries. This is where writing custom M functions becomes invaluable — it's the difference between copy-pasting the same 20 lines of code across dozens of queries and having a clean, reusable function that does the heavy lifting for you.

Custom M functions aren't just about code reuse (though that's huge). They're about creating your own domain-specific language within Power Query. Once you master this skill, you'll find yourself building libraries of functions that encode your organization's business logic, making your queries more readable and your data transformations more reliable.

What you'll learn:

• How to structure and define custom M functions with proper syntax
• Advanced parameter handling including optional parameters and type validation
• Error handling patterns that make your functions production-ready
• Performance optimization techniques for custom functions
• How to build a reusable function library for your organization

Prerequisites

You should be comfortable with basic M language syntax, understand how to write multi-step queries in Power Query, and have experience with built-in M functions like List.Transform and Table.TransformColumns. We'll build on these foundations rather than covering M basics.

Understanding M Function Anatomy

Let's start by examining what makes a custom M function tick. Unlike functions in other languages, M functions are expressions that return other expressions — this functional programming approach is key to understanding how they work.

Here's the basic structure of an M function:

(parameters) =>
let
    // function logic here
    result = /* your transformation */
in
    result

Let's build our first practical function. Suppose you're working with employee data where departments are coded as numbers, but you need descriptive names for reporting:

GetDepartmentName = (departmentCode as number) =>
let
    departmentMap = [
        #"1" = "Engineering",
        #"2" = "Marketing", 
        #"3" = "Sales",
        #"4" = "HR",
        #"5" = "Finance"
    ],
    departmentName = Record.FieldOrDefault(departmentMap, Text.From(departmentCode), "Unknown Department")
in
    departmentName

This function demonstrates several key concepts. First, we're using parameter type annotation (as number) to make our function more robust. Second, we're using a record as a lookup table — this is often more efficient than nested if statements. Third, we're handling the case where an unknown department code is passed in.

To test this function, you can invoke it directly in a new query:

= GetDepartmentName(3)

This should return "Sales". Now let's make it more sophisticated by adding validation:

GetDepartmentName = (departmentCode as number) =>
let
    // Validate input
    validatedCode = if departmentCode < 1 or departmentCode > 5 
                   then error Error.Record("InvalidDepartment", "Department code must be between 1 and 5", departmentCode)
                   else departmentCode,
    
    departmentMap = [
        #"1" = "Engineering",
        #"2" = "Marketing", 
        #"3" = "Sales",
        #"4" = "HR",
        #"5" = "Finance"
    ],
    
    departmentName = Record.Field(departmentMap, Text.From(validatedCode))
in
    departmentName

Tip: Using Error.Record provides structured error information that's easier to debug than simple text errors. The first parameter is the error reason, the second is the message, and the third is the detail (often the problematic value).

Advanced Parameter Handling

Real-world functions often need flexible parameter handling. Let's build a function that standardizes phone numbers — a common data cleaning task. This function will demonstrate optional parameters, parameter validation, and default values.

StandardizePhoneNumber = (
    phoneNumber as text, 
    optional countryCode as text, 
    optional format as text
) =>
let
    // Set defaults for optional parameters
    actualCountryCode = if countryCode = null then "US" else countryCode,
    actualFormat = if format = null then "###-###-####" else format,
    
    // Remove all non-numeric characters
    digitsOnly = Text.Select(phoneNumber, {"0".."9"}),
    
    // Validate we have the right number of digits
    digitCount = Text.Length(digitsOnly),
    
    validatedNumber = if actualCountryCode = "US" and digitCount <> 10 
                     then error Error.Record("InvalidPhone", "US phone numbers must have 10 digits", phoneNumber)
                     else if actualCountryCode = "UK" and digitCount <> 11
                     then error Error.Record("InvalidPhone", "UK phone numbers must have 11 digits", phoneNumber)
                     else digitsOnly,
    
    // Format based on country and format preference
    formattedNumber = if actualCountryCode = "US" then
        Text.Format(actualFormat, {
            Text.Middle(validatedNumber, 0, 3),
            Text.Middle(validatedNumber, 3, 3),
            Text.Middle(validatedNumber, 6, 4)
        })
    else if actualCountryCode = "UK" then
        "+" & Text.Middle(validatedNumber, 0, 2) & " " & 
        Text.Middle(validatedNumber, 2, 4) & " " & 
        Text.Middle(validatedNumber, 6, 3) & " " & 
        Text.Middle(validatedNumber, 9, 3)
    else
        error Error.Record("UnsupportedCountry", "Country code not supported", actualCountryCode)
in
    formattedNumber

This function shows several advanced techniques:

1. Optional parameter handling: We check for null and provide defaults
2. Multi-step validation: We validate both format and business rules
3. Conditional logic: Different formatting based on country
4. Text manipulation: Using Text.Select, Text.Middle, and Text.Format

You can test it with various inputs:

// Basic US number
= StandardizePhoneNumber("(555) 123-4567")
// Returns: "555-123-4567"

// UK number with custom handling
= StandardizePhoneNumber("07911 123456", "UK")
// Returns: "+44 7911 123 456"

// Custom format
= StandardizePhoneNumber("5551234567", "US", "(###) ###-####")
// Returns: "(555) 123-4567"

Error Handling and Defensive Programming

Production-ready functions need robust error handling. Let's build a function that parses custom date strings — a common challenge when integrating data from various systems.

ParseCustomDate = (dateString as text, optional dateFormat as text) =>
let
    actualFormat = if dateFormat = null then "MM/dd/yyyy" else dateFormat,
    
    // Try to parse the date, catching any errors
    parseResult = try 
        let
            cleanedString = Text.Trim(dateString),
            
            // Handle different format patterns
            parsedDate = if actualFormat = "MM/dd/yyyy" then
                Date.FromText(cleanedString, [Format="MM/dd/yyyy"])
            else if actualFormat = "yyyy-MM-dd" then
                Date.FromText(cleanedString, [Format="yyyy-MM-dd"])
            else if actualFormat = "dd/MM/yyyy" then
                Date.FromText(cleanedString, [Format="dd/MM/yyyy"])
            else if actualFormat = "custom_timestamp" then
                // Handle format like "2023Q3_15" meaning 2023, Q3, day 15
                let
                    parts = Text.Split(cleanedString, "_"),
                    yearQuarter = parts{0},
                    day = Number.From(parts{1}),
                    year = Number.From(Text.Start(yearQuarter, 4)),
                    quarter = Number.From(Text.End(yearQuarter, 1)),
                    month = ((quarter - 1) * 3) + 1,
                    constructedDate = #date(year, month, day)
                in
                    constructedDate
            else
                error Error.Record("UnsupportedFormat", "Date format not supported", actualFormat)
        in
            parsedDate
    otherwise null,
    
    // Handle the result
    result = if parseResult[HasError] then
        error Error.Record("DateParseError", 
            "Could not parse date string", 
            [DateString = dateString, Format = actualFormat, OriginalError = parseResult[Error]])
    else
        parseResult[Value]
in
    result

This function demonstrates several error handling patterns:

1. Try-otherwise blocks: Gracefully handle parsing failures
2. Nested error information: Preserve original error details
3. Input validation: Clean and validate input before processing
4. Custom format handling: Support business-specific date formats

Warning: When using try-otherwise, be specific about what you're catching. Catching all errors can hide bugs in your logic.

Building Table-Focused Functions

Many M functions work with entire tables rather than individual values. Let's build a function that performs complex data quality checks on a table:

ValidateCustomerTable = (inputTable as table, optional strictMode as logical) =>
let
    isStrictMode = if strictMode = null then false else strictMode,
    
    // Define validation rules
    validationResults = Table.AddColumn(
        inputTable,
        "ValidationErrors",
        (row) =>
            let
                errors = {},
                
                // Check required fields
                emailError = if Text.Length(Text.Trim(row[Email] ?? "")) = 0 then
                    {"Email is required"} else {},
                
                nameError = if Text.Length(Text.Trim(row[CustomerName] ?? "")) = 0 then
                    {"Customer name is required"} else {},
                
                // Check email format
                emailFormatError = if not Text.Contains(row[Email] ?? "", "@") then
                    {"Email format is invalid"} else {},
                
                // Check phone if in strict mode
                phoneError = if isStrictMode and Text.Length(Text.Trim(row[Phone] ?? "")) = 0 then
                    {"Phone is required in strict mode"} else {},
                
                // Validate customer ID format (must be C followed by 6 digits)
                customerIdError = if not Text.StartsWith(row[CustomerId] ?? "", "C") or 
                                    Text.Length(row[CustomerId] ?? "") <> 7 or
                                    not List.AllTrue(List.Transform(
                                        Text.ToList(Text.Middle(row[CustomerId] ?? "", 1, 6)),
                                        (char) => char >= "0" and char <= "9"
                                    )) then
                    {"Customer ID must be in format C######"} else {},
                
                // Combine all errors
                allErrors = List.Combine({
                    emailError, 
                    nameError, 
                    emailFormatError, 
                    phoneError, 
                    customerIdError
                })
            in
                allErrors
    ),
    
    // Add summary columns
    withErrorCount = Table.AddColumn(
        validationResults,
        "ErrorCount",
        (row) => List.Count(row[ValidationErrors])
    ),
    
    finalTable = Table.AddColumn(
        withErrorCount,
        "IsValid",
        (row) => row[ErrorCount] = 0
    )
in
    finalTable

This function shows how to:

1. Process entire tables: Using Table.AddColumn with row-by-row logic
2. Implement complex validation: Multiple validation rules with conditional logic
3. Handle null values: Using the null coalescing operator ??
4. Build composite results: Creating summary columns based on detailed analysis

To use this function, you'd apply it to a customer table:

ValidatedCustomers = ValidateCustomerTable(CustomerTable, true)

Performance Optimization Techniques

Custom functions can become performance bottlenecks if not designed carefully. Here are key optimization strategies:

Minimize Table Scans

Instead of multiple passes through a table, combine operations:

// Less efficient - multiple table scans
ProcessCustomerData = (customerTable as table) =>
let
    withUppercaseNames = Table.TransformColumns(customerTable, {"CustomerName", Text.Upper}),
    withCleanedEmails = Table.TransformColumns(withUppercaseNames, {"Email", Text.Lower}),
    withFormattedPhones = Table.TransformColumns(withCleanedEmails, {"Phone", (phone) => StandardizePhoneNumber(phone)})
in
    withFormattedPhones

// More efficient - single table scan
ProcessCustomerData = (customerTable as table) =>
let
    transformedTable = Table.TransformColumns(
        customerTable,
        {
            {"CustomerName", Text.Upper},
            {"Email", Text.Lower},
            {"Phone", (phone) => StandardizePhoneNumber(phone)}
        }
    )
in
    transformedTable

Use Appropriate Data Structures

For lookup operations, records are faster than repeated table searches:

// Create lookup function with record-based approach
CreateProductLookup = (productTable as table) =>
let
    // Convert table to record for O(1) lookups
    lookupRecord = Record.FromList(
        Table.Column(productTable, "ProductName"),
        Table.Column(productTable, "ProductId")
    ),
    
    lookupFunction = (productId as text) =>
        Record.FieldOrDefault(lookupRecord, productId, "Unknown Product")
in
    lookupFunction

Leverage Native Functions

When possible, use built-in M functions instead of custom logic:

// Less efficient - custom text cleaning
CleanText = (inputText as text) =>
let
    step1 = Text.Replace(inputText, " ", ""),
    step2 = Text.Replace(step1, "-", ""),
    step3 = Text.Replace(step2, "(", ""),
    step4 = Text.Replace(step3, ")", "")
in
    step4

// More efficient - using Text.Select
CleanText = (inputText as text) =>
    Text.Select(inputText, {"A".."Z", "a".."z", "0".."9"})

Hands-On Exercise

Let's build a comprehensive function that processes sales data. This exercise will combine everything we've learned:

Create a function called ProcessSalesTransaction that:

1. Takes a sales table with columns: TransactionId, SalespersonCode, ProductId, Quantity, UnitPrice, TransactionDate
2. Validates that all required fields are present and properly formatted
3. Calculates total amount and commission (7% of total)
4. Standardizes the salesperson code to uppercase
5. Adds a fiscal quarter column based on the transaction date
6. Returns the enhanced table with validation results

Here's the complete solution:

ProcessSalesTransaction = (salesTable as table, optional commissionRate as number) =>
let
    actualCommissionRate = if commissionRate = null then 0.07 else commissionRate,
    
    // Add calculated and standardized columns
    enhancedTable = 
        let
            withTotalAmount = Table.AddColumn(
                salesTable,
                "TotalAmount",
                (row) => (row[Quantity] ?? 0) * (row[UnitPrice] ?? 0),
                type number
            ),
            
            withCommission = Table.AddColumn(
                withTotalAmount,
                "Commission",
                (row) => row[TotalAmount] * actualCommissionRate,
                type number
            ),
            
            withStandardizedCode = Table.TransformColumns(
                withCommission,
                {"SalespersonCode", (code) => Text.Upper(code ?? "")}
            ),
            
            withFiscalQuarter = Table.AddColumn(
                withStandardizedCode,
                "FiscalQuarter",
                (row) => 
                    let
                        transDate = row[TransactionDate],
                        fiscalQuarter = if transDate = null then null
                                       else "FY" & Text.From(Date.Year(transDate)) & 
                                            "Q" & Text.From(Date.QuarterOfYear(transDate))
                    in
                        fiscalQuarter,
                type text
            )
        in
            withFiscalQuarter,
    
    // Add validation
    validatedTable = Table.AddColumn(
        enhancedTable,
        "ValidationErrors",
        (row) =>
            let
                errors = {},
                
                transactionIdError = if Text.Length(Text.Trim(row[TransactionId] ?? "")) = 0 then
                    {"Transaction ID is required"} else {},
                
                salespersonError = if Text.Length(Text.Trim(row[SalespersonCode] ?? "")) = 0 then
                    {"Salesperson code is required"} else {},
                
                quantityError = if (row[Quantity] ?? 0) <= 0 then
                    {"Quantity must be greater than 0"} else {},
                
                priceError = if (row[UnitPrice] ?? 0) <= 0 then
                    {"Unit price must be greater than 0"} else {},
                
                dateError = if row[TransactionDate] = null then
                    {"Transaction date is required"} else {},
                
                allErrors = List.Combine({
                    transactionIdError,
                    salespersonError,
                    quantityError,
                    priceError,
                    dateError
                })
            in
                allErrors
    ),
    
    finalTable = Table.AddColumn(
        validatedTable,
        "IsValid",
        (row) => List.Count(row[ValidationErrors]) = 0
    )
in
    finalTable

Test your function with sample data:

SampleSales = Table.FromRecords({
    [TransactionId="T001", SalespersonCode="abc", ProductId="P100", Quantity=5, UnitPrice=25.99, TransactionDate=#date(2023,8,15)],
    [TransactionId="T002", SalespersonCode="def", ProductId="P101", Quantity=2, UnitPrice=45.00, TransactionDate=#date(2023,9,22)],
    [TransactionId="", SalespersonCode="ghi", ProductId="P102", Quantity=0, UnitPrice=12.50, TransactionDate=#date(2023,10,5)]
}),

ProcessedSales = ProcessSalesTransaction(SampleSales, 0.08)

Common Mistakes & Troubleshooting

Mistake 1: Not Handling Null Values

// Wrong - will error on null values
BadFunction = (value as text) => Text.Upper(value)

// Right - handles nulls gracefully  
GoodFunction = (value as text) => if value = null then null else Text.Upper(value)

Mistake 2: Inefficient Nested Loops

// Wrong - nested iteration is slow
BadTableJoin = (leftTable as table, rightTable as table, keyColumn as text) =>
    Table.AddColumn(
        leftTable, 
        "JoinedData",
        (leftRow) => 
            List.First(
                List.Select(
                    Table.ToRecords(rightTable),
                    (rightRow) => Record.Field(rightRow, keyColumn) = Record.Field(leftRow, keyColumn)
                )
            )
    )

// Right - use Table.Join for table operations
GoodTableJoin = (leftTable as table, rightTable as table, keyColumn as text) =>
    Table.Join(leftTable, keyColumn, rightTable, keyColumn)

Mistake 3: Ignoring Type Safety

// Wrong - no type validation
RiskyFunction = (param1, param2) => param1 + param2

// Right - explicit typing and validation
SafeFunction = (param1 as number, param2 as number) =>
    let
        validated1 = if not (param1 is number) then 
            error Error.Record("TypeMismatch", "param1 must be a number", param1) 
            else param1,
        validated2 = if not (param2 is number) then 
            error Error.Record("TypeMismatch", "param2 must be a number", param2) 
            else param2
    in
        validated1 + validated2

Debugging Custom Functions

When your function isn't working as expected:

1. Test with simple inputs first: Start with the simplest possible input to verify basic functionality
2. Use intermediate variables: Break complex expressions into steps you can examine
3. Add trace output: Temporarily add columns or variables to see intermediate values
4. Check for null propagation: Null values can cascade through calculations unexpectedly

// Add debugging output to your function
DebugFunction = (inputValue as text) =>
let
    step1 = Text.Trim(inputValue),
    debug1 = [Step="Trim", Input=inputValue, Output=step1],
    
    step2 = Text.Upper(step1), 
    debug2 = [Step="Upper", Input=step1, Output=step2],
    
    // Return debug info along with result during development
    result = [FinalResult = step2, DebugTrace = {debug1, debug2}]
in
    result

Summary & Next Steps

You now have the foundational skills to write robust, production-ready M functions. The key principles we've covered — parameter validation, error handling, performance optimization, and defensive programming — will serve you well as you build more complex functions.

Custom M functions transform how you work with Power Query. Instead of copying and pasting complex logic across queries, you can build a library of reusable functions that encode your organization's business logic. This makes your queries more maintainable, your data transformations more reliable, and your team more productive.

Your next steps should be:

1. Start building a function library: Create a dedicated Power BI file or Excel workbook where you store and test your custom functions
2. Practice with your real data: Take a complex transformation you've built recently and refactor it into a reusable function
3. Learn advanced M patterns: Explore recursive functions, higher-order functions, and more advanced error handling patterns
4. Share with your team: Document your functions and establish patterns for how your team will build and maintain shared function libraries

The investment you make in mastering custom M functions will pay dividends throughout your data career. Every complex data challenge becomes an opportunity to build reusable solutions that make future work easier and more reliable.